Forum Discussion
Need help in DAX
Hi Experts,
I need help with DAX. I've date dimension (Date) and financial fact table (Date, Revenue)
I calculating the last 12-month revenue based on month selection (Both slicers are single selection).
The requirement is to calculate the difference of each month from the previous month.
Below is Last 12-month measure that I'm using it's working fine
Revenue (last n months) :=
VAR MaxFactDate =
CALCULATE ( MAX ( FactFinancial[Date] ), ALL ( 'DimDate' ) )
VAR FDate =
ENDOFMONTH ( 'DimDate'[Date] )
VAR Edate =
EDATE ( FDate, - 12 )
RETURN
IF (
MaxFactDate < MAX ( 'DimDate'[Date] )
&& MaxFactDate >= Edate,
CALCULATE ( [Total Revenues], ALL ( 'DimDate' ) )
)
I want to get previous month total revenue at each month in Last 12 months line chart. I've tried below DAX but no success
Revenue Last 12 PM :=
CALCULATE([Revenue (last n months)], PARALLELPERIOD('DimDate'[Date], -1, MONTH)
Revenue Last 12 PM :=
CALCULATE([Revenue (last n months)], PREVIOUSMONTH('DimDate'[Date])
both are not working as I'm expecting, please help.
Here is another try with no success.
Revenue (last n months) =
CALCULATE (
[Total Revenues],
DATESINPERIOD (
'DimDate'[Date],
MAX ( 'DimDate'[Date] ),
-12,
MONTH
)
)
---------------------------------
Revenue Last 12 PM =
CALCULATE (
[Total Revenues],
DATESINPERIOD (
'DimDate'[Date],
EDATE ( MAX ( 'DimDate'[Date] ), -1 ),
-12,
MONTH
)
)
PBIX file
Ok. I think I got it working. Here is what I did.
1. Used the MonthYear column from your DimDate table in the slicer (optional but one click instead of two)
2. Added a MonthIndex column to your FactFinancial table (so I could easily do prev month calculation w/o Time Intelligence)
MonthIndex = Year(FactFinancial[Date])*12+MONTH(FactFinancial[Date])3. Made this Prev Revenue measurePrev Month Revenue =
VAR maxmonthindex =
MIN ( FactFinancial[MonthIndex] )
RETURN
CALCULATE (
[Total Revenues],
ALL ( DimDate ),
ALL (
FactFinancial[MonthYear],
FactFinancial[MonthYearNo], //needed since used as Sort By Column
FactFinancial[MonthIndex]
),
FactFinancial[MonthIndex] = maxmonthindex - 1
)4. Made these measures for Last 12 M and Last 12 M Prev MonthLast 12 M =
CALCULATE (
[Total Revenues],
DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -12, MONTH )
)Last 12 M Prev Mon =
CALCULATE (
[Prev Month Revenue],
DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -12, MONTH )
)5. Got this resultIf this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
10 Replies
- mahoneypat
Microsoft Employee
Assuming your DimDate table is Marked As Date Table, you can use Time Intelligence functions to do this. Here are example measures for your 12 Month rolling total, and the 12 Month rolling total starting from the previous month.
Running Total 12 M = CALCULATE ( [Total Sales], DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH ) ) Running Total 12 M PM = CALCULATE ( [Total Sales], DATESINPERIOD ( 'Date'[Date], EDATE ( MAX ( 'Date'[Date] ), -1 ), -12, MONTH ) )FYI that I posted a blog recently with a useful "Sandbox" file to practice doing Time Intelligence functions, and used a MinMaxDates measure to confirm I had the right date ranges for the above. You can see it here, if interested.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi mahoneypat ,
I've tried your given DAX you can see the output is attached in question to keep track of what I've tried. Both Dax giving same output.
I've also attached the PBIX file for your reference.
Thanks
- mahoneypat
Your pic shows that you are looking at the day granularity in your table. I assumed you were looking at the month level. Here are some new expressions that will work at the day level.
Running Total 12 M = VAR __thisdate = MIN ( 'DimDate'[Date] ) RETURN CALCULATE ( [Total Revenues], DATESINPERIOD ( 'DimDate'[Date], __thisdate, -12, MONTH ) ) Running Total 12 M PM = VAR __thisdate = MIN ( 'DimDate'[Date] ) RETURN CALCULATE ( [Total Revenues], DATESINPERIOD ( 'DimDate'[Date], EDATE ( __thisdate, -1 ), -12, MONTH ) )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat