cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rbhattacharya
Helper I
Helper I

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.

Month_Selected.JPGYear_Selected.JPG

Regards,

RB

 

 

1 ACCEPTED SOLUTION
AilleryO
Continued Contributor
Continued Contributor

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

View solution in original post

4 REPLIES 4
AilleryO
Continued Contributor
Continued Contributor

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 ?

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
Continued Contributor
Continued Contributor

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

View solution in original post

Hi AilleryO,

 

This worked. Thanks a lot :).

 

Regards,

RB

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors