MAT calculation - using daily data and show them month by month (can't add column and date table)

[Anonymous]

Dear Community, 

I need your help with Moving Annual Total calculation I have to create using Power BI Dataset, I can't add column and don't have a date table.

I have table with three columns - Date (date format), MMYYYY (month and year in text format), Revenue (decimal number).  

And I created a measure: 

MAT  =

  CALCULATE (

  SUM ( 'Sales'[Revenue] ),

    DATESINPERIOD ( 'Sales'[Date], MAX ( 'Sales'[Date] ), -1, YEAR )

)

When I add Date column and MAT to visual it calculates well, day by day, but when I use date hierarchy, MAT shows the same value as revenue like in example below. 

Year	Month	MAT	Revenue
2015	January	190	190
2015	February	170	170
2015	March	190	190
2015	April	200	200

The problem is that I have to show data month by month. 

Is it possible to group dates in measure or make measure work with date hierarchy? 

Thank you in advance for any help. 

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

When using date on its own, there is only one level on your matrix. In this case, all your dates are included in the calculation 

when using the date hierachy, you have 4 levels Year, Quater, Month and Day. So when drilled down to the month level the row is showing only months selected in that level.

To correct this, add allselected function:

MAT = 
CALCULATE (
    SUM ( 'Sales'[Revenue] ),
    FILTER ( ALLSELECTED ( Sales ), SUM ( Sales[Revenue] ) <> BLANK () ),
    DATESINPERIOD ( 'Sales'[Date], MAX ( 'Sales'[Date] ), -1, YEAR )
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for that answer, it works perfectly for me.

[TomMartens]

Hey @Anonymous ,

this article: https://www.daxpatterns.com/time-patterns/ describes almost everything that one can know about date related calculations using DAX.

If the article is not enough, create a pbix using Power BI Desktop that contains sample data but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the file to onedrive or dropbox and share the link. If you are using Excel to create the sample model instead the manual input method share the xlsx as well.

Hopefully, this provides what you are looking for.

Regards,

Tom