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
BirhanAYDS
Helper III
Helper III

Show current month&year when nothing selected on slicer

Hi Guysss! 

 

I need your help, I am exhausted. Situation like this:

I have a visual cost by year & month. I want to show cost of current year if nothing selected on slicer. Slicer from Datetable (DAX) and all tables has year and month column. 

I write this DAX but I couldnt do what I am wanted. 

 

Finans_TotalEmploeeCost _ Avg_ Value_Month = IF(ISCROSSFILTERED(Finans_TotalEmploeeCost[Year]),
CALCULATE(AVERAGE(Finans_TotalEmploeeCost[Value]),FILTER(Finans_TotalEmploeeCost, Finans_TotalEmploeeCost[Year] =ALLSELECTED('Date'[Year]))),
CALCULATE(AVERAGE(Finans_TotalEmploeeCost[Value]),Finans_TotalEmploeeCost[Year]=YEAR(TODAY()))
 

Datetable and thistable has 1-* relation. 

What I want:q1.pngScreenshot_13.png


With calculation(below) 

 

Screenshot_14.png

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi  @BirhanAYDS 

Just adjust the formula as below:

Finans_TotalEmploeeCost _ Avg_ Value_Month = IF(ISFILTERED('Finans_TotalEmploeeCost'[Year]),
CALCULATE(AVERAGE(Finans_TotalEmploeeCost[Value])),
CALCULATE(AVERAGE(Finans_TotalEmploeeCost[Value]),Finans_TotalEmploeeCost[Year]=YEAR(TODAY())))

 

If not your case, please share your sample pbix file and your expected output.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

hi  @BirhanAYDS 

Just adjust the formula as below:

Finans_TotalEmploeeCost _ Avg_ Value_Month = IF(ISFILTERED('Finans_TotalEmploeeCost'[Year]),
CALCULATE(AVERAGE(Finans_TotalEmploeeCost[Value])),
CALCULATE(AVERAGE(Finans_TotalEmploeeCost[Value]),Finans_TotalEmploeeCost[Year]=YEAR(TODAY())))

 

If not your case, please share your sample pbix file and your expected output.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@BirhanAYDS , You can use isfiletered do check weather the slicer has value or .

 

Now Year You have measures like

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
//Only year vs Year, not a level below


This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))

As you want Avg of the month get another measure distinctcount of month using same formula and divide

 

distinctcount (Table[Month Year])

or

distinctcount (Date[Month Year])

use year formula of top these

 

YTD Sales = CALCULATE(distinctcount (Date[Month Year]),DATESYTD('Date'[Date],"12/31"))

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.