Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Rolling 13 months DAX

Hi I am trying to create YoY% calculation measure which will be controled from filter. So if i select March 2019 i want to have 13 months starting from that month backwards (March 2018 till March 2019)


Calculation for actual data was easy:

Sales Rolling 13 ACT = CALCULATE([Sales ACT],DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -13, MONTH))

But when i try same priciple for last year i get entire calculation switched to year before so if is not possible to calcualte YoY variance. As you can see on the picture values for PY are displayed from 201610 till 201710 instead from 201710 to 201810

Rollgin 13 month last year.PNG

Is there a way to make this work, so i can have ACT and PY rolling side by side. From there calcualting YoY ratio would be easy (ACT-PY)/PY
PBIX can be donwloaded here

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

I have looked at you model and making some testing I was abble to get to this way of working it:

  • Disconnect the Rolling Calendar table
  • Use the Rolling Calendar as your slicer
  • Create / Change your measures to the ones below:

 

Sales Rolling 13 ACT = 
VAR Maximum_Date =
    MAX ( 'Rolling Calendar'[Date] )
VAR Minimum_Date =
    DATE ( YEAR ( Maximum_Date ) - 1; MONTH ( Maximum_Date ); 1 )
VAR Selected_Date =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        Selected_Date <= Maximum_Date
            && Selected_Date >= Minimum_Date;
        [Sales ACT];
        BLANK ()
    )

 

 

 

Sales Rolling 13 PY = 
VAR Maximum_Date =
    MAX ( 'Rolling Calendar'[Date] )
VAR Minimum_Date =
    DATE ( YEAR ( Maximum_Date ) - 1; MONTH ( Maximum_Date ); 1 )
VAR Selected_Date =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        Selected_Date <= Maximum_Date
            && Selected_Date >= Minimum_Date;
        [Sales PY];
        BLANK ()
    )

 

 

New measures:

 

Sales Rolling 13 Month ACT (Totals) =
IF (
    HASONEFILTER ( 'Calendar'[Date] );
    [Sales Rolling 13 ACT];
    SUMX ( 'Calendar'; [Sales Rolling 13 ACT] )
)

 

 

 

Sales Rolling 13 Month PY (Totals) = 
IF (
    HASONEFILTER ( 'Calendar'[Date] );
    [Sales Rolling 13 PY];
    SUMX ( 'Calendar'; [Sales Rolling 13 PY] )
)

 

 

Then Just create your percentage base on the Sales Rolling Total:

 

Sales YoY % Rolling = 
IF (
    [Sales Rolling 13 Month ACT (Totals)] = BLANK ();
    BLANK ();
    DIVIDE (
        [Sales Rolling 13 Month ACT (Totals)] - [Sales Rolling 13 Month PY (Totals)];
        [Sales Rolling 13 Month PY (Totals)]
    )
)

 

 

The total measures are created to make the calculations for the last total columns of the Table visual or a card as you can see in the image below and on the PBIX file the total is misscalculated or blanked when using the rolling 13 months sales alone, if you want to place it on a bar chart or similar you can use the rolling sales measures.

 

This will give the result below that you can use on your reports.

 

Rolling_Average.gif

 

Hope this solves your issue, any question please tell me.

 

Regards.

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
MFelix
Super User
Super User

Hi @Anonymous ,

 

Your problem is related with the way you build your measures and your visual since you are using the Calendar table to make your rolling 13 months measures you need to use the column YearMonthInt from the calendar table and not from the Rolling Calendar table.

 

To what I can see from your model you probably are trying to achieve the rolling for last 13 months based on selection of date check this blog post that can help you achieve that.

 

Attach your PBIX files with your table and next to it table with calendar date in it.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi @MFelix Thank you for your answer.

Blog you mentioed was one of my inpirations to get this done, and everything works perfect if I apply it to ACT data.

But if i try to apply same principle to PY measure (PY measure use SAMEPERIODLASTYEAR function) i bump into the problem.

 

On left side you can see ACT and PY measurese working as expected when i dont have Rolling filter applied via DAX measures. Last column YoY% i what i try to accomplish, and It is easy for basic formulas. Since values ACT and PY are in the same row.

Problem is when i try to create rolling 13 YoY% since PY and ACT data are not in the same row. I am not looking for average or sum, but just month values filtered by slicer. So If i select December 2018 in slicer i get December 2017-December 2018 values for both ACT and PY, and eventualy YoY%.

End reslut should be line chart with YoY% measure and no more that 13 values across x axis, those tables you are seeing now are just to make it easier to unerstad a problem. 

 

 

1rjflG.png

Hi @Anonymous ,

 

I have looked at you model and making some testing I was abble to get to this way of working it:

  • Disconnect the Rolling Calendar table
  • Use the Rolling Calendar as your slicer
  • Create / Change your measures to the ones below:

 

Sales Rolling 13 ACT = 
VAR Maximum_Date =
    MAX ( 'Rolling Calendar'[Date] )
VAR Minimum_Date =
    DATE ( YEAR ( Maximum_Date ) - 1; MONTH ( Maximum_Date ); 1 )
VAR Selected_Date =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        Selected_Date <= Maximum_Date
            && Selected_Date >= Minimum_Date;
        [Sales ACT];
        BLANK ()
    )

 

 

 

Sales Rolling 13 PY = 
VAR Maximum_Date =
    MAX ( 'Rolling Calendar'[Date] )
VAR Minimum_Date =
    DATE ( YEAR ( Maximum_Date ) - 1; MONTH ( Maximum_Date ); 1 )
VAR Selected_Date =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        Selected_Date <= Maximum_Date
            && Selected_Date >= Minimum_Date;
        [Sales PY];
        BLANK ()
    )

 

 

New measures:

 

Sales Rolling 13 Month ACT (Totals) =
IF (
    HASONEFILTER ( 'Calendar'[Date] );
    [Sales Rolling 13 ACT];
    SUMX ( 'Calendar'; [Sales Rolling 13 ACT] )
)

 

 

 

Sales Rolling 13 Month PY (Totals) = 
IF (
    HASONEFILTER ( 'Calendar'[Date] );
    [Sales Rolling 13 PY];
    SUMX ( 'Calendar'; [Sales Rolling 13 PY] )
)

 

 

Then Just create your percentage base on the Sales Rolling Total:

 

Sales YoY % Rolling = 
IF (
    [Sales Rolling 13 Month ACT (Totals)] = BLANK ();
    BLANK ();
    DIVIDE (
        [Sales Rolling 13 Month ACT (Totals)] - [Sales Rolling 13 Month PY (Totals)];
        [Sales Rolling 13 Month PY (Totals)]
    )
)

 

 

The total measures are created to make the calculations for the last total columns of the Table visual or a card as you can see in the image below and on the PBIX file the total is misscalculated or blanked when using the rolling 13 months sales alone, if you want to place it on a bar chart or similar you can use the rolling sales measures.

 

This will give the result below that you can use on your reports.

 

Rolling_Average.gif

 

Hope this solves your issue, any question please tell me.

 

Regards.

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @Anonymous ,

 

Just a small information if you want you can make a single measure for CY / PY with the following measures:

 

Sales Rolling 13 ACT = 
VAR Maximum_Date =
    MAX ( 'Rolling Calendar'[Date] )
VAR Minimum_Date =
    DATE ( YEAR ( Maximum_Date ) - 1; MONTH ( Maximum_Date ); 1 )
VAR Selected_Date =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        HASONEFILTER ( 'Calendar'[Date] );
        VAR ROLLING_SALES =
            IF (
                Selected_Date <= Maximum_Date
                    && Selected_Date >= Minimum_Date;
                [Sales ACT];
                BLANK ()
            )
        RETURN
            ROLLING_SALES;
        SUMX (
            'Calendar';
            VAR ROLLING_SALE =
                IF (
                    Selected_Date <= Maximum_Date
                        && Selected_Date >= Minimum_Date;
                    [Sales ACT];
                    BLANK ()
                )
            RETURN
                ROLLING_SALE
        )
    )
Sales Rolling 13 PY = 
VAR Maximum_Date =
    MAX ( 'Rolling Calendar'[Date] )
VAR Minimum_Date =
    DATE ( YEAR ( Maximum_Date ) - 1; MONTH ( Maximum_Date ); 1 )
VAR Selected_Date =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        HASONEFILTER ( 'Calendar'[Date] );
        VAR ROLLING_SALES =
            IF (
                Selected_Date <= Maximum_Date
                    && Selected_Date >= Minimum_Date;
                [Sales PY];
                BLANK ()
            )
        RETURN
            ROLLING_SALES;
        SUMX (
            'Calendar';
            VAR ROLLING_SALE =
                IF (
                    Selected_Date <= Maximum_Date
                        && Selected_Date >= Minimum_Date;
                    [Sales PY];
                    BLANK ()
                )
            RETURN
                ROLLING_SALE
        )
    )

If you want I can resend the PBIX file with this measures.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you. This helped me out too. 🙂 

Anonymous
Not applicable

Hi @MFelix 

Thanks againg for your help.

I checked your solution, it is clean and simple as it can get  Smiley Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.