Need help in DAX

[Anonymous]

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

https://www.dropbox.com/s/fy5olrgt7wgd9nq/Finance.pbix?dl=0

[mahoneypat]

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.

https://community.powerbi.com/t5/Community-Blog/A-Self-Contained-quot-Sandbox-quot-PBIX-File-to-Prac...

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

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

[Anonymous]

@mahoneypat ,

No success same results as of 12 months but now 0.00 comes on April 30, 2019.

Can u please download my attached power BI file and see where is the problem.

Link: https://www.dropbox.com/s/fy5olrgt7wgd9nq/Finance.pbix?dl=0

Let me clear u I've monthly fact data and I'm creating date table as a calculated table.

DimDate = 
ADDCOLUMNS (
CALENDAR (MIN(FactFinancial[Date]),MAX(FactFinancial[Date])),"DateYear", FORMAT ( [Date], "YYYY" ),"MonthYear",FORMAT([Date],"MMM YY"))

 Thanks

[mahoneypat]

I wasn't able to open the pbix.  It prompted me for credentials.  As I don't have them (appropriately), it could not load the data model.  Is this a Direct Query model?

Regards,

Pat

[Anonymous]

@mahoneypat ,

Yes, I'm using Direct Query. I've just changed to import and upload again. Please check it should work now and let me know.

https://www.dropbox.com/s/fy5olrgt7wgd9nq/Finance.pbix?dl=0

Thanks

[mahoneypat]

Found the issue.  You need to use the Month Year column from your DimDate table in the table visual, not the one from the FactFinancial table.  Once I did that and cleared the slicers, it worked as expected.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@mahoneypat ,

I got what you said but this is not my requirement. I've tried this way. Sorry to say, we can't clear slicers since we need to show the last 12 months based on month-year selection. By adding Month Year from DimDate, if I select the single month from slicer it'll give only selected month data then we are losing the last 12-month purpose. It should show last 12 month data based on selected month year from slicer plus previous month data at each month level. 

[mahoneypat · Accepted solution]

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 measure

Prev 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 Month

Last 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 result

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Great work, if possible please share PBIX file

also can you please look into this issue

https://community.powerbi.com/t5/Desktop/Calculate-revenue-from-last-3-to-6-month-based-on-selection...