Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a report with a date slicer selecting last 12 months.
But I would like to create a measure to calculate the count of Serial Number for last 24 months.
May I know how to do it?
Thanks
Solved! Go to Solution.
Hi,@PBI_newuser
The time interval in the measure is not same as it in the last 24 months (calendar) distinct count.
I have modified the demo, please try itHow to calculate count of serial number with filter of created date.pbix
Best Regards,
Link Chen
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xulin-mstf ,
Why I can't get the same distinct count when i filter the date to last 24 months?
Should be same as the last 24 months (calendar) distinct count.
How to count the serial number for last 24 months for Company A if the slicer Company=Company A is selected?
Hi,@PBI_newuser
The time interval in the measure is not same as it in the last 24 months (calendar) distinct count.
I have modified the demo, please try itHow to calculate count of serial number with filter of created date.pbix
Best Regards,
Link Chen
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @v-xulin-mstf .
If I would like to have the distinct count of Serial Number for last 24 months (calendar), how can I modify the measure? Is it possible? (Eg. 10778 counts)
Hi,@PBI_newuser
You don't need to modify the measure if the distinct count of Serial Number for last 24 months (calendar) calculate the value for the last 24 months.
In the above pictures, the distinct count of Serial Number for last 24 months (calendar) calculate the values between 2018/12/1 and 2020/11/30.
The latest demo I provided you with correctly calculates the distinct count of Serial Number for last 24 months.
In addition, it can also count the serial number for last 24 months for Company A if the slicer Company=Company A is selected, please try it.
Best Regards,
Link Chen
Hi @v-xulin-mstf ,
I can't get the last 24 months count for Serial Number when I select last 12 months date.
Could you please see the pbix file below?
Hi,@PBI_newuser
I have modified the measure as:
Hi, @PBI_newuser
You can use the following method to meet your needs:
Step1: Depend on your need, create a table and a slicer as:
Step2: Create a measure as:
count of serial number =
CALCULATE (
SUM ( 'Table1'[Serial Number] ),
FILTER (
ALL ( Table1 ),
SELECTEDVALUE ( Table1[Month].[Date] )
= DATEADD ( Table1[Month].[Date], 24, MONTH )
)
)
Step3: Drag the measure into a card:
Best Regards,
Link Chen
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xulin-mstf , thank you.
Is it possible to have dynamic filtering for other slicers except date in the measure?
For example, I select certain Product Group, Country and Company, the measures calculate the serial number in last 24 months.
Hi, @PBI_newuser
If the measure has been applied, all fields added to the corresponding table can be dynamically filtered by the slicer.
Best Regards,
Link Chen
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@PBI_newuser , with help from date table like this example
Rolling 24 = CALCULATE(count(Sales[Serial Number]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-24,MONTH))
Rolling 24 = CALCULATE(count(Sales[Serial Number]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-24,MONTH))
Rolling 24 = CALCULATE(count(Sales[Serial Number]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-24,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.
Hi @amitchandak ,
How to count the Serial number for last 24 months even the date is filter to 12 months?
@PBI_newuser , with a date table marked as date it should else we need to add all date
Rolling 24 = CALCULATE(count(Sales[Serial Number]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-24,MONTH), All('Date'))
Hi @amitchandak, I got an error for the measure.
Calculation error in measure. A date column containing duplicate dates was specified in call to function 'DATESINPERIOD'
@PBI_newuser , Make sure you are using date table.
Or Try a measure like this
measure =
var _min1 = minx(allselected('Date'),'Date'[Date])
var _max = maxx(allselected('Date'),'Date'[Date])
var _min = date(year(_min1),month(_min1)-24,day(_min1))
return
CALCULATE(count(Table[Serial Number]), filter(all('Date'),'Date'[Date] >=_min && 'Date'[Date] <=_max))
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |