cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Distinct count for -6 to -12 months / -12 to -18 months

Evening,

 

Hoping not to difficult a question for some talented PowerBI Guru's.

 

I am trying to calculate the distinct count of orders for the last 6 months and the last 6(7) to 12 months, so that is a rolling figure and always the last 0 to 12 months in two seperate calculated measures but effectivly a rolling sum of orders for the last 12 months.

 

At the moment i have a measure for the previous Fiscal year like the below;

Orders in 2014 = CALCULATE(DISTINCTCOUNT('Table'[OrderColumn]), DATESBETWEEN('Table'[DateColumn], DATE(2013, 10, 01), DATE(2014, 09, 30)))

 

But thinking a rolling average would be better so it adapts as the market changes rather than basing targets on a static previous 3 years when the last 6/12 months is the most relevant to project the next 6 to 12 months.

 

Thanks in Advance,

 

J

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Think ive just cracked it...

 

Dist Orders Last 6 months = CALCULATE(DISTINCTCOUNT('Table'[OrderColumn]), DATESINPERIOD('Table'[DateColumn], TODAY(), -6, MONTH))

 

Added the - and remove the ""

 

Just validating the output now manually.

 

Thanks,

 

J

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Think ive just cracked it...

 

Dist Orders Last 6 months = CALCULATE(DISTINCTCOUNT('Table'[OrderColumn]), DATESINPERIOD('Table'[DateColumn], TODAY(), -6, MONTH))

 

Added the - and remove the ""

 

Just validating the output now manually.

 

Thanks,

 

J

Hi @Anonymous,

 

Great to hear the problem got resolved! Could you accept your helpful replies as solution to help others who may also have the similar issue easily find the answer and close this thread?Smiley Happy

 

Regards

Anonymous
Not applicable

This is what i have so far, but just returning a Null value...

 

Dist Orders Last 6 months = CALCULATE(DISTINCTCOUNT('Table'[OrderColumn]), DATESINPERIOD('Table'[DateColumn], TODAY(), "6",MONTH))

 

*Table and Column names changed*

 

Hoping im missing a () somewhere and nothing to major.

 

Thanks,

 

J

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.