Moving Annual Total Measure Acting Strangely

[Ufuk]

Hello,

I’m trying to make a worst-case scenario analysis of sales. I have the “Estimation” table which holds the estimated ratio of sales over the same month last year. I want to create a measure for moving annual total of sales which will take actual production until current month then the worst-case estimation for future months. “Sales Worst Case” measure seems to be working well. “Sales MAT Worst” is also working well until the first change of ratio (i.e. 01/06/2020)

Any help will be much appreciated…

Sales Worst Case =

calculate (

    Sales[Sales Total],

    filter (

        Dates,

        or(

            AND(

                Dates[Date].[MonthNo]<MONTH(today()),

                Dates[Date].[Year]=YEAR(today())

                ),

            Dates[Date].[Year]<YEAR(today())

            )

        )

)

+

calculate (

    Sales[Sales Total Last Year] * max(Estimation[Worst]),

    filter (

        Dates,

        OR(

            AND(

                Dates[Date].[MonthNo]>=MONTH(today()),

                Dates[Date].[Year]=YEAR(today())

                ),

            Dates[Date].[Year]>YEAR(today())

            )

        )

)

Sales MAT Worst =

CALCULATE (

Sales[Sales Worst Case],

DATESINPERIOD(Dates[Date], LASTDATE(Dates[Date]),-1,YEAR)

)

Estimation Table:

Date	Worst
01/04/2020	0.95
01/05/2020	0.95
01/06/2020	0.90
01/07/2020	0.90
01/08/2020	0.85
01/09/2020	0.85
01/10/2020	0.75
01/11/2020	0.75
01/12/2020	0.75

[Anonymous]

I would kindly suggest you read first about creating good models and the proper handling of time-intelligence:

https://www.youtube.com/watch?v=78d6mwR8GtA

https://www.youtube.com/watch?v=_quTwyvDfG0

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

https://radacad.com/basics-of-time-intelligence-in-dax-for-power-bi-year-to-date-quarter-to-date-mon...

Working with incorrect models is always not only a pain in the you-know-where but it also leads to inefficient and complex DAX, many a time to errors which you won't be even able to spot.

Please learn to build correct models first. For instance, you should never rely on the automatic columns created by PBI for dates. You should always create your own calendar. There are too many reasons to explain here. Please start with the above materials.

Best
D

[Ufuk]

Dear @Anonymous,

Many thanks for your suggestions and I'll go through the material. However, in this case, I don't think anything is wrong with the model.

The problem is that a measure which works well, doesn't return the same results when used in MAT calculation...

Best, 

[Anonymous]

Hi @Ufuk.

Having seen this in your code "Dates[Date].[MonthNo]", I know immediately that your model is INCORRECT.

Best
D

[amitchandak]

@Ufuk , Check if datesytd or total ytd can help

example

YTD Sales = CALCULATE(Average(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))

[Ufuk]

Dear @amitchandak,

As I'm making an estimation in to the future, datesytd or total ytd do not help.

Best,