cancel
Showing results for
Did you mean:  Helper IV

## Distinct count cumulative with datesYTD for a period selected

hello guys ,

I woud like to count in cumulative the number of invoice for the given period

the scenario is the user select a month of any year . The mesure should count in cumulative the number of invoice from the last 1/10 to the last day of the month selected

exemple : the user select 06/2020, the period will be 1/10/2019 to 30/06/2020

If the filter is not used, by default, the date will be the last day of the last month.

Exemple : today is 22/09/2021 so the period is 1/10/2020 to 31/08/2021

My date table =

calendar(min(INVOICE[Invoice Date]), EOMONTH(TODAY(),-1))
so it's okay for the last day of the last month

here is my measure

``````test calcul =
VAR _date_selected=MAX('Date'[Date])

//if month of date selected is 10 || 11 || 12
var _maxdate_a = DATE(YEAR(_date_selected),9,30)
// if month of date selected is anothers months
var _maxdate_b = DATE(YEAR(_date_selected)-1,9,30)
//--->
var _maxdate = IF(MONTH(_date_selected)>=10 && MONTH(_date_selected)<=12,_maxdate_a,_maxdate_b)

//----------------------------------

//if month of date selected is 10 || 11 || 12
var _mindate_a = DATE(YEAR(_date_selected)-1,10,1)
// if month of date selected is anothers months
var _mindate_b = DATE(YEAR(_date_selected)-2,10,1)
//--->
var _mindate = IF(MONTH(_date_selected)>=10 && MONTH(_date_selected)<=12,_mindate_a,_mindate_b)

//calcul cumulative with the period filtered
var result = CALCULATE(DISTINCTCOUNT(INVOICE[N° Invoice ]),DATESYTD('Date'[Date],"30/09"),FILTER(ALL('Date'),'Date'[Date]>= _mindate && 'Date'[Date]<=_maxdate))
return result``````

here is a matrice that I made with some measures If the date selected is between 10/2020 and 8/2021, I would like that the measure displays 16 587

I hope that the topic was clear for you, don't hesitate to ask questions

Thank you

1 ACCEPTED SOLUTION  Community Support

Hi, @trungnguyen0000 ;

You could try to modify measure like belows:

``````test calcul2 =
VAR _date_selected =
MAX ( 'Date'[Date] ) //if month of date selected is 10 || 11 || 12

VAR _maxdate =
IF (
ISFILTERED('Date'[Date]),EOMONTH(TODAY(),-1),
IF(MONTH ( _date_selected ) >= 10
&& MONTH ( _date_selected ) <= 12,
_date_selected,
EOMONTH(_date_selected,-12)
)) //----------------------------------
//if month of date selected is 10 || 11 || 12
VAR _mindate_a =
DATE ( YEAR ( _date_selected ) - 1, 10, 1 ) // if month of date selected is anothers months
VAR _mindate_b =
DATE ( YEAR ( _date_selected ) - 2, 10, 1 ) //--->
VAR _mindate =
IF (
MONTH ( _date_selected ) >= 10
&& MONTH ( _date_selected ) <= 12,
_mindate_a,
_mindate_b
) //calcul cumulative with the period filtered
VAR result =
CALCULATE (
DISTINCTCOUNT(  [invoice] ),

FILTER ( ALL ( 'INVOICE'), [Date] >= _mindate && [Date] <= _maxdate )
)
RETURN
result``````

The final output is shown below: If I understand not right , can you share more informations or simple file without sensitive information?
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3  Community Support

Hi, @trungnguyen0000 ;

You could try to modify measure like belows:

``````test calcul2 =
VAR _date_selected =
MAX ( 'Date'[Date] ) //if month of date selected is 10 || 11 || 12

VAR _maxdate =
IF (
ISFILTERED('Date'[Date]),EOMONTH(TODAY(),-1),
IF(MONTH ( _date_selected ) >= 10
&& MONTH ( _date_selected ) <= 12,
_date_selected,
EOMONTH(_date_selected,-12)
)) //----------------------------------
//if month of date selected is 10 || 11 || 12
VAR _mindate_a =
DATE ( YEAR ( _date_selected ) - 1, 10, 1 ) // if month of date selected is anothers months
VAR _mindate_b =
DATE ( YEAR ( _date_selected ) - 2, 10, 1 ) //--->
VAR _mindate =
IF (
MONTH ( _date_selected ) >= 10
&& MONTH ( _date_selected ) <= 12,
_mindate_a,
_mindate_b
) //calcul cumulative with the period filtered
VAR result =
CALCULATE (
DISTINCTCOUNT(  [invoice] ),

FILTER ( ALL ( 'INVOICE'), [Date] >= _mindate && [Date] <= _maxdate )
)
RETURN
result``````

The final output is shown below: If I understand not right , can you share more informations or simple file without sensitive information?
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  Super User

@trungnguyen0000 , You can use time intelligence with date table

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"9/30"))

Last parameter means your year start from 1-oct

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!  Helper IV

your solution doesn't work with this requirement. It calculates for this year but not for the last year   