Total in Matrix Incorrect when using Measure with condition based on Today()

[ppdas2112]

Hi experts!

Need your urgent help!

My data model is as below

I have a simple measure: 

Actual/Forecast = IF(MONTH(MAX(DateTable[Date]))<MONTH(TODAY()),SUM(Actuals[Actuals]),SUM(Forecast[Forecast]))

When I plot this in Matrix, the total at Year level doesn't add up correctly. Kindly help!!

@amitchandak @johnt75 @tamerj1 @Jihwan_Kim @PaulDBrown @truptis @Ashish_Mathur @AlexisOlson @parry2k @Greg_Deckler @BA_Pete @mahoneypat @Vera_33 @Vijay_A_Verma @edhans @jennratten @smpa01 @Thingsclump 

Thanks in advance!

[Vera_33 · Accepted solution]

Hi @ppdas2112 

It's calculation on different gran, you need a Month column in DateTable, guess you are using hierachy now

Actual/Forecast = 
IF (
    COUNTROWS ( ALLSELECTED ( DateTable ) ) = COUNTROWS ( DateTable ),
    SUMX (
        VALUES ( DateTable[Month] ),
        CALCULATE (
            IF (
                MAX ( DateTable[Month] ) < MONTH ( TODAY () ),
                SUM ( Actuals[Actuals] ),
                SUM ( Forecast[Forecast] )
            )
        )
    ),
    IF (
        MAX ( DateTable[Month] ) < MONTH ( TODAY () ),
        SUM ( Actuals[Actuals] ),
        SUM ( Forecast[Forecast] )
    )
)

[parry2k · Accepted solution]

@ppdas2112 here is the measure you need:

Actual/Forecast = 
SUMX ( 
   VALUES ( DateTable[Date] ),
   IF(MONTH(DateTable[Date])<MONTH(TODAY()),
        CALCULATE ( SUM(Actuals[Actuals]) ),
        CALCULATE ( SUM(Forecast[Forecast])
   )
)

 subscribe to my channel link below to learn why your original measure didn't work and what this measure is doing. I will be doing a video on this in the next few days.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[parry2k · Accepted solution]

@ppdas2112 here is the measure you need:

Actual/Forecast = 
SUMX ( 
   VALUES ( DateTable[Date] ),
   IF(MONTH(DateTable[Date])<MONTH(TODAY()),
        CALCULATE ( SUM(Actuals[Actuals]) ),
        CALCULATE ( SUM(Forecast[Forecast])
   )
)

 subscribe to my channel link below to learn why your original measure didn't work and what this measure is doing. I will be doing a video on this in the next few days.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[ppdas2112]

@parry2k Thank you!!

[Vera_33 · Accepted solution]

Hi @ppdas2112 

It's calculation on different gran, you need a Month column in DateTable, guess you are using hierachy now

Actual/Forecast = 
IF (
    COUNTROWS ( ALLSELECTED ( DateTable ) ) = COUNTROWS ( DateTable ),
    SUMX (
        VALUES ( DateTable[Month] ),
        CALCULATE (
            IF (
                MAX ( DateTable[Month] ) < MONTH ( TODAY () ),
                SUM ( Actuals[Actuals] ),
                SUM ( Forecast[Forecast] )
            )
        )
    ),
    IF (
        MAX ( DateTable[Month] ) < MONTH ( TODAY () ),
        SUM ( Actuals[Actuals] ),
        SUM ( Forecast[Forecast] )
    )
)

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[ppdas2112]

Hi Ashish,

Please find the pbix file

https://drive.google.com/drive/folders/16eFgKRCZITOOmnTC_VHx_1vS0FEiYQ1s?usp=sharing

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[ppdas2112]

Thank you @Ashish_Mathur !!

[Ashish_Mathur]

You are welcome.