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

0
votes

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

enter image description here

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 replace -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?

2
timepowerbidaxbusiness-intelligencecalculated-field

2 Answers

1
votes
Count Previous Year =
IF (
    HASONEVALUE ( Dates2[Month] ),
    IF (
        [Count] <> BLANK (),
        CALCULATE ( [Count], SAMEPERIODLASTYEAR ( Dates2[Date] ) )
    ),
    IF (
        HASONEVALUE ( Dates2[Year] ),
        CALCULATE (
            [Count],
            DATESBETWEEN (
                Dates2[Date],
                EDATE ( MIN ( Dates2[Date] ), -12 ),
                EOMONTH ( MAX ( [FactTable[Date] ), -12 )
            )
        )
    )
)
0
votes

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()

                           )

                       )

!Output obtained as desired]1