Forum Discussion

Nagesh_01's avatar
Nagesh_01
Frequent Visitor
3 years ago
Solved

Calculate Cumulative total from December to December. Example, From Dec'2020 to Dec'2021 Cumulative

Hi, 
Could someone please help me on this DAX? I need to calculate YTD/Cumulative total from December to December which means Previous year's Last month (December) to next year's last moth (December). Example Dec'2020 to Dec'2021. 

Please find below example in spreadsheet. Please help to calculate YTD/Cumulative amount like below mentioned.

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I tried to create a sample pbix file like below, and please check the below picture and the attached pbix file.

     

     

     

    YTD and previous December: =
IF (
    NOT ISBLANK ( [Amount measure:] ) && HASONEVALUE ( 'Calendar'[Date] ),
    CALCULATE ( [Amount measure:], DATESYTD ( 'Calendar'[Date] ) )
        + CALCULATE (
            [Amount measure:],
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Year]
                    = MAX ( 'Calendar'[Year] ) - 1
                    && 'Calendar'[Month number] = 12
            )
        )
)

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Thank you for your feedback.

    The measure has HASONEVALUE(Calendar[Date]) function as a condition. This condition is not to show the total row (when it has more than one date value) in the table visualization.

    If you write a measure without HASONEVALUE, it will show as expected.

     

    YTD and previous December: = 
IF (
    NOT ISBLANK ( [Amount measure:] ),
    CALCULATE ( [Amount measure:], DATESYTD ( 'Calendar'[Date] ) )
        + CALCULATE (
            [Amount measure:],
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Year]
                    = MAX ( 'Calendar'[Year] ) - 1
                    && 'Calendar'[Month number] = 12
            )
        )
)

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I tried to create a sample pbix file like below, and please check the below picture and the attached pbix file.

     

     

     

    YTD and previous December: =
IF (
    NOT ISBLANK ( [Amount measure:] ) && HASONEVALUE ( 'Calendar'[Date] ),
    CALCULATE ( [Amount measure:], DATESYTD ( 'Calendar'[Date] ) )
        + CALCULATE (
            [Amount measure:],
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Year]
                    = MAX ( 'Calendar'[Year] ) - 1
                    && 'Calendar'[Month number] = 12
            )
        )
)

     

    • Nagesh_01's avatar
      Nagesh_01
      Frequent Visitor
      3 years ago

      Hi Jihwan_Kim 
      Awesome!! it works pretty well. Thank you so much for your great help.
      However, Could you please check this issue? it's working fine when we use Date field, but when we use year and month number /name, then it's showing blank. 
      Please find below screenshot. 

       



      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        3 years ago

        Hi,

        Thank you for your feedback.

        The measure has HASONEVALUE(Calendar[Date]) function as a condition. This condition is not to show the total row (when it has more than one date value) in the table visualization.

        If you write a measure without HASONEVALUE, it will show as expected.

         

        YTD and previous December: = 
IF (
    NOT ISBLANK ( [Amount measure:] ),
    CALCULATE ( [Amount measure:], DATESYTD ( 'Calendar'[Date] ) )
        + CALCULATE (
            [Amount measure:],
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Year]
                    = MAX ( 'Calendar'[Year] ) - 1
                    && 'Calendar'[Month number] = 12
            )
        )
)