cancel
Showing results for
Did you mean:
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!

 DATE EVENTS 12/1/2020 4 12/2/2020 2 12/3/2020 2 12/4/2020 8 12/5/2020 0 12/6/2020 1 12/7/2020 3

2 ACCEPTED SOLUTIONS
Super User III

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 =
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

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] )``````

5 REPLIES 5
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] )``````

Helper II

@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!

Super User III

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

Helper II

@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.

Super User III

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 =
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

Announcements

#### 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.