Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I have requirement wherein I need to show values in my Power BI report based on the date selection.
I tried creating a DAX measure as below. Though the data shows the correct previous month value by default, the visuals always show (Blank) whenever I try to select any other date using slicer, even though those months have data.
Last Month Count =
VAR default_date =
MAX(Table1[Year_Month])-1
RETURN
IF (ISFILTERED (Table1[Month Name]),DISTINCTCOUNT(Table1[Project Name]),
CALCULATE (DISTINCTCOUNT(Table1[Project Name]),FILTER (Table1,Table1[Year_Month]=default_date )
)
Attached the images of the data and output in PBI for reference.
Please assist with your suggestions that can help resolve the same.
Thanks!
Solved! Go to Solution.
@Sm95
I am afraid you cannot use date hierarchy in this slicer. As workaround, if you want to show the slicer in Year+MonthName format. You may create a calculated column and use it as slicer.
YearMonth = FORMAT([Month Name],"YYYYMMM")
Then use ISFILTERED ('Table'[YearMonth]) in the measure.
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
@Sm95
I am afraid you cannot use date hierarchy in this slicer. As workaround, if you want to show the slicer in Year+MonthName format. You may create a calculated column and use it as slicer.
YearMonth = FORMAT([Month Name],"YYYYMMM")
Then use ISFILTERED ('Table'[YearMonth]) in the measure.
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
@Sm95
You have used ISFILTERED (Table1[Month Name]), you should change it to Year_Month column according to your dax.
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
@Sm95 , Please use date table and time intelligence and date table
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Next month value = CALCULATE(sum(''Table''[total hours value]),nextmonth('Date'[Date]))
Next to next month value = CALCULATE(sum(''Table''[total hours value]),nextmonth(dateadd('Date'[Date],1,MONTH)))
previous to previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth(dateadd('Date'[Date],-1,MONTH)))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Also check why TI can fail - https://youtu.be/OBf0rjpp5Hw
Thanks for the response.Is it mandatory for this functionality to work only if a date table is created separately? Is there any way using the DAX, that I can achieve this without the date table i.e. within the original table itself? The functionality to fetch the default value works, but only the filtering part seems to fail right now.
Thanks!!
@Sm95 , time intelligence and date table is best way.
If you choose a date , assume from your table in a slicer.
Now to get last month's data you need to use all as slicer already restricted date. All will come with its own setup things you need to work with
example auuming month name is a date
measure =
var _1 = eomonth(maxx(allselected(Table1),Table1[monthname]),-1)
return
CALCULATE (DISTINCTCOUNT(Table1[Project Name]),FILTER (all(Table1[monthname]),eomonth(Table1[monthname],0)=_1 ))
or
measure =
var _1 = eomonth(maxx(allselected(Table1),Table1[monthname]),-1)
return
CALCULATE (DISTINCTCOUNT(Table1[Project Name]),FILTER (all(Table1),eomonth(Table1[monthname],0)=_1 ))
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |