CODEWITHSUNDEEP
powerbidax
Nick Mochizuki
Nick Mochizuki

Reputation: 13

Moving Annual Total with DAX Power BI

I'm trying to calculate MAT (Moving Annual Total) using DAX, but can't realize exactly what I want. I have the data as below.

enter image description here

What I'm trying to is calculate the total of treatment for each 12 month, like "Jan 21 - Dec 21" which has 'date' from 2020/1/1 to 2020/12/1. i.e. 'A' appears 4 times in that period, so the result will be like below.

enter image description here

Then I'd like to continue this for each latest 12 months, and finally visualize it like below. enter image description here

I've read that I should use CALCULATE and DATESPERIOD in DAX, can't calculate exactly though. The following code is the one I tried and failed.

Moving Annual Total = CALCULATE(
COUNTA('2017-2022Q1'[idnum]),
DATESINPERIOD('2017-2022Q1'[mat].[Date], LASTDATE('2017-2022Q1'[mat].[Date]), 12, MONTH))

Could someone kindly give me an advise to realize this? Thanks.

*This is the sample file. https://drive.google.com/file/d/1gDNeBe5KiKBqx3cZ7G0SMiSQ23w96NF4/view?usp=sharing

Upvotes: 0

Views: 999

Answers (1)

Ozan Sen
Ozan Sen

Reputation: 2615

In your dax measure, I recommend you first to create a date table and create a one-to-many relationship with the fact table(your table: '2017-2022Q1') ; then filter the date table in your calculate measure, not the fact table('2017-2022Q1') directly as you did here. You should follow the best practice.

        For Example: How to create a date table using dax:
        Date =
        VAR MinYear = YEAR ( MIN ( '2017-2022Q1'[date] ) )
        VAR MaxYear = YEAR ( MAX ( '2017-2022Q1'[date] ) )
        RETURN
        ADDCOLUMNS (
            FILTER (
                CALENDARAUTO( ),
                AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
            ),
            "Calendar Year", "CY " & YEAR ( [Date] ),
            "Month Name", FORMAT ( [Date], "mmmm" ),
            "Month Number", MONTH ( [Date] )
        )

Your final code could be like this:

        Moving Annual Total = CALCULATE(
        COUNTA('2017-2022Q1'[idnum]),
        DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -12, MONTH)
        
        )

if you define the start date using the lastdate() function, then It returns the last date in your filter context. you should also specify the interval as (-12) month to extract all dates within 1 year period.

Hope It helps.

Upvotes: 0

Related Questions