cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stalerik
Helper II
Helper II

DAX formula to count back number of occurrences

Hello,

 

I have a column of dates and a column of events.  I am looking for a way to count back the number of events and report the first date for the last  5, 10, 15 event etc.  For example, in the following table, counting back for last 10 events on 12/7/2020 would return the date of 12/4/2020.  Counting back for last 15 events would be 12/3/2020.

 

This seems simple but I'm having a hard time finding the right function.  Thanks for the help!

 

DATEEVENTS
12/1/20204
12/2/20202
12/3/20202
12/4/20208
12/5/20200
12/6/20201
12/7/20203

 

2 ACCEPTED SOLUTIONS
AlB
Super User III
Super User III

Hi @stalerik 

1. Create a one-column table to be used as slicer to select the number of events:

SlicerT =
SELECTCOLUMNS ( GENERATESERIES ( 5, 20, 5 ), "Num Events", [Value] )

2. Place Table1[DATE] in a table visual 

3. Create this measure and place it in the table visual:

Measure = 
VAR baseDate_ =
    SELECTEDVALUE ( Table1[DATE] )
VAR slicerVal_ =
    SELECTEDVALUE ( SlicerT[Num Events] )
VAR auxT =
    ADDCOLUMNS (
        FILTER ( ALL ( Table1 ), Table1[DATE] <= baseDate_ ),
        "@Cumul",
            VAR currentDate_ = [DATE]
            RETURN
                CALCULATE (
                    SUM ( Table1[EVENTS] ),
                    Table1[DATE] >= currentDate_,
                    Table1[DATE] <= baseDate_,
                    ALL ( Table1 )
                )
    )
RETURN
    MAXX ( FILTER ( auxT, [@Cumul] >= slicerVal_ ), [DATE] )

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

CNENFRNL
Super User III
Super User III

Hi, @stalerik , you might want to test if this alternative fits in your scenario. (attached pbix file)

Back Date = 
VAR __c = [Count back]
VAR __d = [Base Date]
VAR __period =
    FILTER (
        DISTINCT ( Table1[DATE] ),
        CALCULATE (
            SUM ( Table1[EVENTS] ),
            DATESBETWEEN ( Table1[DATE], Table1[DATE], __d )
        ) <= __c
    )
RETURN
    MINX ( __period, Table1[DATE] )

 Screenshot 2020-12-26 222649.png

View solution in original post

5 REPLIES 5
CNENFRNL
Super User III
Super User III

Hi, @stalerik , you might want to test if this alternative fits in your scenario. (attached pbix file)

Back Date = 
VAR __c = [Count back]
VAR __d = [Base Date]
VAR __period =
    FILTER (
        DISTINCT ( Table1[DATE] ),
        CALCULATE (
            SUM ( Table1[EVENTS] ),
            DATESBETWEEN ( Table1[DATE], Table1[DATE], __d )
        ) <= __c
    )
RETURN
    MINX ( __period, Table1[DATE] )

 Screenshot 2020-12-26 222649.png

View solution in original post

@AlB and @CNENFRNL  Thank you very much for your quick help.  Both answers above produced the same "back date" but I was still having problems integrating it into my project.  I was defining a measure for the back date, and then trying to use that measure as a variable in my calculated column with Direct Query.  That did not work.  Instead of using the measure as the variable, I put the whole method to calculate the back date into the calculated column and it now looks to be working correctly.  Thank you both for your assistance!

AlB
Super User III
Super User III

@stalerik 

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

@AlB Thank you for the answer.  I was testing this out without a slicer by setting a fixed slicerVal_ number but unfortunately this is part of a DirectQuery model and it's getting stuck on the MAXX function.  I'm looking into alternatives, but I'd appreciate it if you have any quick ideas.

 

Edit:  Forgot to mention that I was then using this date as a variable in another column.

AlB
Super User III
Super User III

Hi @stalerik 

1. Create a one-column table to be used as slicer to select the number of events:

SlicerT =
SELECTCOLUMNS ( GENERATESERIES ( 5, 20, 5 ), "Num Events", [Value] )

2. Place Table1[DATE] in a table visual 

3. Create this measure and place it in the table visual:

Measure = 
VAR baseDate_ =
    SELECTEDVALUE ( Table1[DATE] )
VAR slicerVal_ =
    SELECTEDVALUE ( SlicerT[Num Events] )
VAR auxT =
    ADDCOLUMNS (
        FILTER ( ALL ( Table1 ), Table1[DATE] <= baseDate_ ),
        "@Cumul",
            VAR currentDate_ = [DATE]
            RETURN
                CALCULATE (
                    SUM ( Table1[EVENTS] ),
                    Table1[DATE] >= currentDate_,
                    Table1[DATE] <= baseDate_,
                    ALL ( Table1 )
                )
    )
RETURN
    MAXX ( FILTER ( auxT, [@Cumul] >= slicerVal_ ), [DATE] )

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors