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

Accepted Solutions
Anonymous
Not applicable

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

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

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

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

Anonymous
Not applicable

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

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

Microsoft v-ljerr-msft
Microsoft

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

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)