Forum Discussion

Sulsa's avatar
Sulsa
Frequent Visitor
2 years ago
Solved

Calculation Groups and YTD KPI Goals

Hello All,

 

I have a calculation group that includes, MTD,LY and YTD. I would like the YTD calculation to show the KPI "Goal" for the whole year, but only show the measure for the dates where there is data (which is typically to the end of the previous month). Currently it repeats the latest value for the rest of the year.

 

I have dimensions to filter "valid dates" to remove future dates from YTD calculations and an "is budgetary" dimension to filter budgetary values.  I wrote this DAX, but it does not like that the filter expression are from different tables.

 

 

CALCULATE(
    TOTALYTD(
        SELECTEDMEASURE()
        , Dates[Date]

    )
    ,Dates[Valid Period] = 1 || Basis[IsBudgetary] = TRUE() 
)

 

 

There a way to adjust the dax to make it work with both the measure and the kpi? I've tried adding "Dates[Valid Period] = 1" to the measure, but the TOTALYTD seems to override that.

 

 

Thank you for your assistance.

  • Sulsa's avatar
    Sulsa
    2 years ago

    Appreciate your help, but I wasn't able to get that to work. What did though was to use an IF in the measure (and not in the KPI or calculation group) like this:

        IF(
        min( Dates[Valid Period] ) = 0, BLANK(),
        - CALCULATE(
            <some aggragation here>,<some filter here>
                
            )
        )
    )

     

2 Replies

    • Sulsa's avatar
      Sulsa
      Frequent Visitor
      2 years ago

      Appreciate your help, but I wasn't able to get that to work. What did though was to use an IF in the measure (and not in the KPI or calculation group) like this:

          IF(
        min( Dates[Valid Period] ) = 0, BLANK(),
        - CALCULATE(
            <some aggragation here>,<some filter here>
                
            )
        )
    )