Power BI DAX Tutorial (8/50) - How to Compare Previous Month Quarter Year Values

[@Prateek987]

Thank a lot! One of the rare videos I have seen where monthly percent difference is being calculated!!

[@AnalyticswithNags]

You are welcome!

[@dineshvardam2176]

Sir, your videos are also asset for me , thanks a lot for creating such helpful videos .I am searching such video series for DAX from last 1 year.

[@AnalyticswithNags]

Thanks keep learning

[@gouravhalder9195]

In case of Calculating % difference, will it not be divided by [Sales Previous month] measure?

[@AnalyticswithNags]

Yes

[@balamira297]

That's what even I was thinking.

[@mrnawaz5341]

Where from I get date dim and sales data​@@AnalyticswithNags

[@GopiKrish895]

Hi, @AnalyticswithNags thanks for helping me out. Now I have an issue, after creating a Date Hierarchy and create this previous month Calculated column. The date column shows February first instead of January, same for months like June first then May. How should I correct this. All these happens in table viz in report view. I created Date table using DAX- calendarauto().

[@siddheshdandagavhal9804]

% Diff from prev month can be calculated using the previousmonth function. Similarly we need to create different measures to get %difference for year and quarter. Can we use the previous period function from last video to dynamically get the %difference?

[@UmaMukherjee-y4l]

5:57 we should compare previous month as per current month... but u calculated based on previous month...

[@AnalyticswithNags]

it depends on the business , you can do either ways

[@bharaths5906]

Hi, In current video we are seeing about DAX on PREVIOUS MONTH comparision, so whats the difference between this DAX on PREVIOUS MONTH and PREVIOUS MONTH (DATES MTD) function. Does both gives the same result

[@rashmipatil-c6b]

PREVIOUSMONTH Function: Purpose: This function returns a table containing the dates from the previous month in the current context. Usage: It is used when you want to compare the entire previous month with another period, like the current month or a specific month. Example: If you use PREVIOUSMONTH(Date[Date]), it will return all the dates from the previous month based on the context of the Date column. 2. DATESMTD (Dates Month-To-Date) Function: Purpose: This function returns a table containing a set of dates that represent the current month up to the current date in the month. Usage: It is used when you want to compare month-to-date performance with a similar period in another month (like the same period last month or year). Example: If you use DATESMTD(Date[Date]), it will return all dates from the start of the current month up to the current date.

[@zainulabdeen3344]

from where can i get the sales and dim.date table

[@sharulchauhan]

hi I want to one thing, how can we make this more dynamic? If we have date filter and depending on whatever number of days/months user selects, we show him the result of similar previous period (e.g.: if user selects data for november and december then we compare with september and october, or if user just wants to see only march month then we compare with only feb month).

[@AnalyticswithNags]

Yes. Soon i will make a video on this

[@manvitha_1234]

@@AnalyticswithNags Can please share the video link for the above question

[@saisrinu7526]

What if one of the values is null for me, it is showing infinity in the % diff where one of the values is empty.....

[@AnalyticswithNags]

Use divide function that will handle

[@shubhambhangale4682]

Hello, how can we enable the totals for measures? As shown in your video, we can't see totals when we use PREVIOUS{month/day/year/quarter} functions. However, I see totals for SAMEPERIODLASTYEAR function.

[@AnalyticswithNags]

Not sure about your issue

[@shubhambhangale4682]

@@AnalyticswithNags At 3.55(3 mins 55 secs), we can't see totals for Sales PrevMonth column. How can we show totals for this column?

[@cosmos7685]

Can you please send the data source file because link in description contained previous file it doesn't have sales and dimdate table.

[@AnalyticswithNags]

For DimDate creation follow this link. kzbin.info/www/bejne/eJ_UpXWufMeiZq8 For sales fact use any of the sales excel file

[@MoonShine-bs6cl]

If i want to calculate growth% for year and quarter combined how to do??

[@shubhabratadey]

How has the "DimDate" table been created? If you can share, that'll be helpful

[@AnalyticswithNags]

Check the channel with date dimensions creation

[@VinodKumar-zn8ty]

Hello Sir, for %sales = (Current value - previous value)/previous value right sir But in the video it is (Current value - previous value)/Current value Which is the right one to use sir

[@AnalyticswithNags]

Previous value if you want to see the % against that

[@harshithabobolu7106]

Hi Nag, what's the correct formula for calculating %diff

[@AnalyticswithNags]

This is the one I shown in demo

[@duleguntlasaisree7138]

Hi Sir, I have a query here. What is the diff between PREVIOUSYEAR and SAMPLEPERIODLASTYEAR

[@AnalyticswithNags]

Previous year can go one year back only(only year). Where as same period last year will go one year / one quarter / month based on what period you analyse.

[@amirtamboli938]

Hi Sir, I have a Product Table with Connected Dim Group, Category, and SubCategory. Product table connected to Sales Table as sales table having product ID. Sales Table Connected to DimDate Table. I have created measures as instructed. All measures are working correctly with the date hierarchy. But when I am trying to get Sale by Group or category or Subcategory. Selected a Qtr 1 / dates but the result is showing same sale value for each (row) of Group or category or Subcategory at PrevMonth Sale & Sales value. pls help, thank you.

[@AnalyticswithNags]

Not sure what you are asking about. Can you able to explain it better

[@ravitejatukaram9683]

Hi Nag can share me this excel link in description is different....

[@AnalyticswithNags]

Use the previous video description.

[@mastanvallishake9191]

Please attach the data source for my purpose to calculate the same in my desktop. Thank you

[@AnalyticswithNags]

Check the description of the video

[@rakeshchaudhary1110]

I want to know if am selecting march then sales come of three month camculative sales if I am selecting jun then camculative sales of jan to jun

[@AnalyticswithNags]

Answer in this video kzbin.info/www/bejne/nnuvgHWso8SZbs0

[@gopalakrishnarajarapu491]

Iam using previous year but. . can't show visuals ..what problem .. please tell me

[@AnalyticswithNags]

Not sure what you are trying. Can't help much in the comments. Join the telegram group ( link in description) and post your queries there

[@mabellinnmaung]

Why SUMX function is not used?Will it be the same?

[@arajashekar-m8z]

no

[@frustratedgaiii4088]

Why we use calculate in every formula , why can't we only sum the sales amount and then add our expression

[@AnalyticswithNags]

We can simply use sum if we don’t use any filters. It is good practice to use calculate

[@LFValadas]

What if instead of the month, we want to compare by weeks?

[@AnalyticswithNags]

You can compare using week number

[@LFValadas]

@@AnalyticswithNags How? There is no previousweek...

[@supakornsingtheintrakul4872]

Thank you very much🥹

[@AnalyticswithNags]

Thank you! Cheers!

[@Nindoish]

The % diff formula is wrong. Percentage difference formula is (Current - Previous)/Previous you used (Current-Previous)/Current

[@AnalyticswithNags]

Thanks for highlighting. But, I hope you understand how to calculate % diff using DAX

[@Baki_Hanma_3]

how to compare previous week sales with current week?

[@AnalyticswithNags]

It is not easy to answer. soon will make a video on this and keep watching our videos.

[@venkatareddykummitha5863]

I want the total sales of the previous month in the card visual.

[@AnalyticswithNags]

Get the Previous month begging and end in variable and Use that variables in calculate filters

[@smartrose7997]

can you pls help me with a dax for latest records in previous month..?

[@AnalyticswithNags]

Check playlists of this channel and explore topics on the similar line requested by you.

[@irfandaud7380]

% Difference is not calculated correctly for the Month and Quarter as well . T Sale PrevQuarter = CALCULATE(DAXDATA[T Total Sale],PREVIOUSQUARTER(DAXDATEGEN[Date])) T % QTD Diff = DIVIDE(DAXDATA[T Total Sale] - DAXDATA[T Sale PrevQuarter] , DAXDATA[T Sale PrevQuarter]))

[@AnalyticswithNags]

Check the formula for T total sales.

[@venkatareddykummitha5863]

Interview based Dax questions, please

[@AnalyticswithNags]

We do teaching to gain knowledge on the subject, not as per interview and interview questions. if you are focused only on getting job and interview and questions refer to some other place

[@ashishmohan4707]

Hi very useful video and very practical need everyone for every project ......very big thanks to share with us. If it can possible can you plzz provide us excel data which u used for this Very great ful for us...👌👌👌👌👌👌👌👌💐💐💐💐🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂

[@AnalyticswithNags]

Thanks for your feedback. Data source file link Updated in Description. Keep Learning.

[@venkatareddykummitha5863]

hi nag

[@AnalyticswithNags]

hi venkatareddy kummitha

[@BhanusagarPagadala]

Worst tutorial ever

[@AnalyticswithNags]

Thanks for letting me know, I will try to improve better