Moving Annual Total in stacked area chart

[Anonymous]

I need help to calculate MAT (moving annual total) where the user can seletec a specific month in the "MMYYYY" filter slicer and then see the MAT in a stacked area chart.

E.g. if the user select "022020" in the filter slicer the graph should show data from feb 2019 to feb 2020. If the user select "122019" the graph should show data from dec 2018 to dec 2019.

I have calculated MAT by:

Sales MAT =

CALCULATE(

'Fact Sales'[Sales];

DATESINPERIOD('Dimension Date'[date];LASTDATE('Dimension Date'[date]);-1;YEAR)

)

This calculation works fine in a table. My issue is when I what to show the data in a graph. Because when the user for example select "022020" then the graph only shows data from feb 2020 and not for the past 12 months!

Below is a picture of the MAT calculation that works fine in the table but not in the graph.

[v-alq-msft]

Hi, @Anonymous 

Based on my research, I think it is a normal phenomenon. I created data to reproduce your scenario.

Table:

 

Calendar:

Calendar = CALENDARAUTO()

There is a one-to-one relationship between two tables.

I got the same result with the measure you provided.

Sales MAT = 
CALCULATE(
    SUM('Table'[Sales]),
    DATESINPERIOD(
        'Calendar'[Date],
        LASTDATE('Calendar'[Date]),
        -1,YEAR
    )
)

When you select one date, the gragh will display the result for the corresponding date. If you want to display more dates, you need to multi-select dates.

If I misunderstand your thoughts, please show me your sample data and expected result. Do mask sensitive data before uploading. Thanks

Best Regards

Allan

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

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490