Microsoft Power BI - DAX Time Intelligence measure - reflect proper % change ; non-YTD measures

[snph1777]

I have a Power BI visual as below. There are 3 matrices. I have a DateDimension (or) Calendar table called Dates2.

I use two measures, one a regular measure (called 'Count'), the other a parallel period comparison of the measure

(called 'Count_PreviousYear').   I use SAMEPERIODLASTYEAR DAX function for the latter.

1)
Count = COUNTA(TableX[ColumnY])

--Measure with name 'Count'--

2)
Count_PreviousYear = CALCULATE
                                                           (
                                                             [Count],
                                                             SAMEPERIODLASTYEAR(Dates2[Date])
                                                            )

--Measure with name 'Count_PreviousYear'--
--this measure uses Time Intelligence function - SAMEPERIODLASTYEAR--

Both 'Count' and 'Count_PreviousYear' (obviously) are not YTD (YearToDate) values.

A third measure for the percentage change across periods is computed as below:

3)

PercentageChange = IF(

                                       ISBLANK([Count]) || ISBLANK([Count_PreviousYear]),

                                       BLANK(),

                                       (([Count] - [Count_PreviousYear])/[Count])

                                      )

--kindly ignore the fact that a keyword used as a measure name; I have used the name 'Count' only for clarity--
--in my actual report, I have proper names--

The % change measure works fine, but one issue:

For the period change from 2020 to 2021, i.e. in the third row of the last matrix (for the row value 2021), the total (i.e. the % change value) is not appropriate.

I need to repalce -737.21% with - 23.98 %.

This is because , I need to compute the Total for 2020, only by adding the values for the months of January and February, i.e. 428 + 430 = 858. (not 5794, which is for all the 12 months).

Since 2021 has only two months - January and February, I don't want to compare two months of 2021, with all the 12 months of 2020. Rather, I want two months of 2021 to be compared with the corresponding 2 months of 2020.

Essentially I need {(692-858)/692} * 100 = -23.98%.

Currently, I see {(692-5794)/692} * 100 = -737.21%

Can someone help me achieve this ?

I have some intermediate knowledge in DAX Time Intelligence, but that is not good enough to solve this problem.

[snph1777 · Accepted solution]

Count_PreviousYear = IF (

                                           (HASONEVALUE(Dates2[Year]) = TRUE
                                           &&
                                           HASONEVALUE(Dates2[MonthName]) = TRUE),

                                           CALCULATE
                                           (

                                           [Count],
                                           SAMEPERIODLASTYEAR(Dates2[Date])

                                           ),

                                           IF (

                                                (HASONEVALUE(Dates2[Year]) = TRUE
                                               &&
                                                HASONEVALUE(Dates2[MonthName]) = FALSE),

                                                CALCULATE (

                                                                       [Count],
                                                                       DATESBETWEEN (
                                                                                                     Dates2[Date],
                                                                                                    EDATE (MIN(Dates2[Date]), -12),
                                                                                                    EOMONTH (MAX(SourceData[Date]), -12)
                                                                                                    )

                                                                     ),

                                                BLANK()

                                               )

                                      )

By slightly modifying the measure above, and keeping everything else same, I got the solution.

SoureData is the table in my Power BI model that has all the data.

The Date column in SourceData table and the Date column in the Dates2 table are related.

The challenge I had is, my measures are not YTD.

I could not directly use anything from SQL BI posts mentioned by @jdbuchanan71 

[jdbuchanan71]

The fix will work for all time intelligent functions.  All they do is take in a list of dates and shift them back and forth.  You need to stop the list of dates for 2021 from including dates past today which is what that article shows you how to do.

[snph1777]

@AlexisOlson       -   Hello Alexis, Can you help me with this ? It should be simple for you.

[jdbuchanan71]

@snph1777 

Take a look at this article.  It adresses the issue you are seeing with "future" dates throwing off calculations and goes into how to fix it.

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

[snph1777]

I looked at this in the morning. But I am not using YTD (Year To Date). Mine is a regular measure, with a Same-Period-LastYear comparison