Forum Discussion

rbhattacharya's avatar
rbhattacharya
Icon for Helper I rankHelper I
5 years ago
Solved

YEAR DAX NOT WORKING AS EXPECTED

Hello,

 

I've a date column (PeriodFormatted) which I'm using as a filter for a report. Based on the selected month, I want to calculate value of a particular column for that YEAR to which the month belongs to. I'm using the following DAX expression for calculating value for the whole year:

 

GrossBillingsProjection = CALCULATE(SUM('Budgeting v_IncomeStatement'[Gross-Billings]), YEAR('Budgeting v_IncomeStatement'[PeriodFormatted]))
 
I can see that the DAX picks up the correct value ($357K) only when the whole YEAR (2021) is selected in the period slicer and not just the month. If I'm selecting just the month, I'm getting value only for the selected month ($36.9K) and not for the whole year. Not sure if the DAX expression is correct. Kindly help. 

 

Please refer to the attached screenshots for reference.

Regards,

RB

 

 

Need Help
  • AilleryO's avatar
    AilleryO
    5 years ago

    The answer realy depends on your data, but here are some solutions :

    Using a KEEPFILTERS allows you to keep some of them.

     

    Or maybe you can just remove the month filter ?

    In that case it will be 

    Total Global= CALCULATE([Global Sales],
    ALL(DateTable[Month])) instead of ALL(DateTable[Year-Month])
    Tell us if it works

4 Replies

  • AilleryO's avatar
    AilleryO
    Icon for Memorable Member rankMemorable Member
    5 years ago

    Hi,

     

    Not sure about your need, but if you want your measure to always show the total of the year, you can add a REMOVEFILTER functions, or an ALL...

    For instance :

    Total Global= CALCULATE([Global Sales],
    ALL(DateTable[Year-Month]))//ALL() removes filter Year-Month (from a table)

     

    Is that of any help ?

    • rbhattacharya's avatar
      rbhattacharya
      Icon for Helper I rankHelper I
      5 years ago

      Hello AilleryO,

       

      Thanks a lot for the response. I would need to retain the "YEAR" filter though. Any suggestions what the DAX query should be?

       

      Regards,

      RB

      • AilleryO's avatar
        AilleryO
        Icon for Memorable Member rankMemorable Member
        5 years ago

        The answer realy depends on your data, but here are some solutions :

        Using a KEEPFILTERS allows you to keep some of them.

         

        Or maybe you can just remove the month filter ?

        In that case it will be 

        Total Global= CALCULATE([Global Sales],
        ALL(DateTable[Month])) instead of ALL(DateTable[Year-Month])
        Tell us if it works