Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PBI_newuser
Post Prodigy
Post Prodigy

How to calculate count of serial number with filter of created date = last 24 months?

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

1 ACCEPTED SOLUTION

Hi,@PBI_newuser 

The time interval in the measure is not same as it in the last 24 months (calendar) distinct count.

1.png2.png

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.

 

View solution in original post

15 REPLIES 15
PBI_newuser
Post Prodigy
Post Prodigy

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?

PBI_newuser_0-1609144046226.png

 

Hi,@PBI_newuser 

The time interval in the measure is not same as it in the last 24 months (calendar) distinct count.

1.png2.png

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

Okay, thank you so much for your help @v-xulin-mstf !

PBI_newuser
Post Prodigy
Post Prodigy

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?

 

https://wetransfer.com/downloads/694f8de5864e6fa1627a825a850087f720201228070820/b318173dfeba78a2e149...

Hi,@PBI_newuser 

I have modified the measure as:

SN Count last 24 months =
CALCULATE (
DISTINCTCOUNT ( data[Serial Number] ),
FILTER (
ALL ( data ),
SELECTEDVALUE ( data[Date].[Date] )
= DATEADD ( data[Date].[Date], 24, MONTH )
)
)
Please try it.
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.
v-xulin-mstf
Community Support
Community Support

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:

1.png2.png

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:

3.png

Here is the demo, please try it:How to calculate count of serial number with filter of created date = last 24 months? .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 , 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.

1.png

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.

amitchandak
Super User
Super User

@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_0-1608710687918.png

 

@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))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.