how to I make a date by December?

[Anonymous]

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!

[v-chenwuz-msft · Accepted 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.

[amitchandak]

@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