Forum Discussion

VickyDev18's avatar
VickyDev18
Icon for Advocate II rankAdvocate II
2 years ago

Modify MTD, YTD, FY behavior in a table with months

I have created standard MTD, YTD, FY measures as shown below as calcualted items in a calculation group.

 

MTD = CALCULATE(SELECTEDMEASURE(), DATESMTD('Date'[Date]))
YTD = CALCULATE(SELECTEDMEASURE(), DATESYTD('Date'[Date]))
FY = CALCULATE(SELECTEDMEASURE(), ALLEXCEPT('Date', 'Date'[Year]))


I'd like to know if it's possible to modify the behaviour of these measures such that they show values as shown below when a month is part of the table. Sample File Link

 

 

 

Need Help

3 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    2 years ago

    Hey VickyDev18 ,

     

    please describe in more detail what you mean by "behave."

     

    Next, when I remove the selection of May it becomes apparent that "FY" does not show the same values as in the image. What is the expectation/business rule for FY.

     

    Regards,

    Tom

    • VickyDev18's avatar
      VickyDev18
      Icon for Advocate II rankAdvocate II
      2 years ago

      FY should basically show the full year total when a month is NOT part of the visual or more specifically the row in the visual e.g. total row, but when month is in the row it should the monthly value.   

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        HI VickyDev18,

        You cna add if statement to check the current hierarchy level and redirect to different calculation expression and output correspond result based on current hierarchy level.

        Clever Hierarchy Handling in DAX - SQLBI

        Regards,

        Xiaoxin Sheng