Cumulative distinct count with different period depending on month

[Anonymous]

Hi guys,

After searching for long time, I didn't find a solution for a requirement so I will appreciate if someone could help me

I would like to get the cumulative count of invoice per hierarchy Year/Month with some conditions.

The users could filter on only one year and one month, if none of filters are selected, we take today()

Condition : we have a date selected dd/mm/YYYY, the period to use is the last 1/10 to the last day of the last month of this date.

example : if the date selected is from 1/11/2020 to 31/12/2020, I have to count from 1/10/2020 to 31/10/2020 (if we are in novembre ) or 30/11/2020 (if we are in december)

if the date is dd/04/2021, I have to count from 1/10/2020 to 31/03/2021

if the date is dd/10/2021, I have to count from 1/10/2020 to 30/09/2021

I have Invoice table with a column Invoice Date.

I created a Date tab =

ADDCOLUMNS(

calendar(min(Invoice[Invoice Date]), today()), 

"MonthNum", MONTH([Date]),
"Month", FORMAT([Date], "MMMM"),

"Year", YEAR([Date])
)

Then, I created a relationship to link these 2 tables

I tried many formula and this one is best I could make :

calcul cumul = 
var month_selected = SELECTEDVALUE('Date'[MonthNum])
var year_selected = SELECTEDVALUE('Date'[Year])
var to_count= DISTINCTCOUNT(Invoice[N° invoice]

// if none filter, use today()
var x= IF(OR(ISBLANK(year_selected),ISBLANK(month_selected)),TODAY(),DATE(year_selected,month_selected,1))

//if the month of date selected is either november or december, use this var 
var nov_dec = CALCULATE(to_count,FILTER(ALL(Invoice[Invoice Date]),Invoice[Invoice Date]>=DATE(YEAR(x),10,1) && Invoice[Invoice Date]<=DATE(YEAR(x),MONTH(x)-1,DAY(EOMONTH(x,-1)))))

//if the month of date selected is january, use this var
var jan= CALCULATE(to_count),FILTER(ALL(Invoice[Invoice Date]),Invoice[Invoice Date]>=DATE(YEAR(x)-1,10,1) && Invoice[Invoice Date]<=DATE(YEAR(x)-1,12,31)))

//if the month of date selected is from february to october, use this var
var feb_to_oct= CALCULATE(to_count),FILTER(ALL(Invoice[Invoice Date]),Invoice[Invoice Date]>=DATE(YEAR(x)-1,10,1) && Invoice[Invoice Date]<= DATE(YEAR(x),MONTH(x)-1,DAY(EOMONTH(x,-1)))))

var result = IF(MONTH(x)=11 || MONTH(x)=12,nov_dec,IF(MONTH(x)=1,jan,feb_to_oct))

return result 

Here is an example of result that I would like to get

And can you tell me which hierarchy of date that I should use to display the result ? Invoice[Invoice Date] or Date[Date] ?

How could I do to get the result change only if both filter of year and of month are used ?

I hope that my explaination is clear.

Thank you very much in advance !

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures

to_count= DISTINCTCOUNT(Invoice[N° invoice]

YTD to_count = calculate([to_count],datesytd(calendar[date],"30/9"))

If your Calendar date goes only uptil today's date, then this is all that you should need.  Just ensure that your slicers are built from the Calendar Table.  So your Calendar Table should have columns for Financial Year, Month Name and Month number.  Ensure that your sort the Month name by the Month number.

Hope this helps.