Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Datetable and thistable has 1-* relation.
What I want:
With calculation(below)
Solved! Go to Solution.
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
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
@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"))
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |