YEAR DAX NOT WORKING AS EXPECTED

[rbhattacharya]

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

[AilleryO]

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]

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 · Accepted solution]

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

[rbhattacharya]

Hi AilleryO,

This worked. Thanks a lot :).

Regards,

RB