Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, all
I am wondering how should I create the measure.
Data is available from March.
I wish I could see the full year of the date.
I want it to be displayed as 0 in January and February there is no data, and to be blank on other days(May to Dec).
I have calender table like below.
And I made measure like
IF(COUNTROWS('Table')=BLANK(),0,COUNTROWS('Table'))
I need a DAX solution, Thanks!
Solved! Go to Solution.
Hi @Anonymous,
Maybe you can try this expressoin.
Measure =
var _c = COUNTROWS(FILTER('Fact table',[Date].[MonthNo]=SELECTEDVALUE('Calendar'[Date].[MonthNo])))
return
IF(_c=BLANK()&& SELECTEDVALUE('Calendar'[Date].[MonthNo])<month(TODAY()),0,_c)
Don't forget to adjust the type of the x-axis to continuous. And customize the end of the x-axis to be the maximum date of the calendar table. Use fx icon for this.
result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous,
Maybe you can try this expressoin.
Measure =
var _c = COUNTROWS(FILTER('Fact table',[Date].[MonthNo]=SELECTEDVALUE('Calendar'[Date].[MonthNo])))
return
IF(_c=BLANK()&& SELECTEDVALUE('Calendar'[Date].[MonthNo])<month(TODAY()),0,_c)
Don't forget to adjust the type of the x-axis to continuous. And customize the end of the x-axis to be the maximum date of the calendar table. Use fx icon for this.
result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , using a date table ,
example
Measure =
var _lastmonth = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],12- month(Today()),MONTH)))
return
if(isblank(_lastMonth), 0, SUM(Sales[Sales Amount]) )
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.
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |