cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
trungnguyen0000
Helper IV
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

trungnguyen0000_2-1632327451833.png

 

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
v-yalanwu-msft
Community Support
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:

vyalanwumsft_0-1632709459674.png

 

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.

View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
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:

vyalanwumsft_0-1632709459674.png

 

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.

View solution in original post

amitchandak
Super User
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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.