Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
Memorable Member
Memorable Member

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

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

Hi AilleryO,

 

This worked. Thanks a lot :).

 

Regards,

RB

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.