Running sum last 6 months measure on two date dimensions simultaneously

[ChristianRHouen]

This question is related to a previous question: https://community.powerbi.com/t5/Desktop/Running-sum-last-6-months-measure/m-p/994940#M473159

I would like to create a running sum for the last 6 months based on two date dimensions. (The previous question was only on one date dimension)

I have a data table (DataTable) where each row has two date stamps; Date A and date B. Date A will in 99% of the cases be before Date B (but I don't think this should matter.)

I also have a separate Calendar Table.

Date A has an inactive relation to Calendar Table, Date B has an active relation.

I would like to make a measure that counts rows in DataTable where both Date A and Date B is within the last 6 months. Outcome of this is that I would like to build a graph that shows this measure over time, using my Calendar Table.

E.g. If today is 01-01-2020 then
"Date A: 01-08-2019, Date B: 01-09-2019" will be counted but
"Date A: 01-05-2019, Date B: 01-09-2019" will not be counted and
"Date A: 01-08-2019, Date B: 01-05-2019" will also not be counted.

Data is confidential, hence I cannot share it. I can build mock dataset if required.

I hope you guys can help!

[MFelix]

Hi @ChristianRHouen ,

If you inactive both relationships with the Calendar table you can use the following measure:

Count =
COUNTROWS (
    FILTER (
        SUMMARIZE ( 'Table'; 'Table'[ID]; 'Table'[Date A]; 'Table'[Date B] );
        'Table'[Date A] <= SELECTEDVALUE ( 'calendar'[Date] )
            && 'Table'[Date A]
                >= ( SELECTEDVALUE ( 'calendar'[Date] ) - 180 )
            && 'Table'[Date B] <= SELECTEDVALUE ( 'calendar'[Date] )
            && 'Table'[Date B]
                >= ( SELECTEDVALUE ( 'calendar'[Date] ) - 180 )
    )
)

[ChristianRHouen]

I'm not sure this solves my issue.

This seem to only go back 6 months from today and sum the data, i.e. making it quite static.

I would like to track it over time, so for instance, the accumulated number of rows today maybe 150, but 6 months ago it was something else.

Am I misinterpreting your suggestion?

[MFelix]

Hi @ChristianRHouen ,

The measure is dinamic based on the calendar date you select and goes 6 month back:

If you place the calendar date in a chart you will get the calculation based on the date selected it's this part of the calculation that is making that dinamic selection:

 SELECTEDVALUE ( 'calendar'[Date] )

The only thing that is hard coded is the 180 days from the selected date but that can also be dinamic based on a slicer or some other variable.

[ChristianRHouen]

@MFelix Do you think you're still able to help me?

[MFelix]

Hi @ChristianRHouen 

Sorry for the late response I didn't see your message in the middle of the other notifications.

I was missing a parameter in the filter redo your measure to:

Count =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            ALL ( data[ID]; data[DateA]; data[DateB] );
            data[ID];
            'data'[DateA];
            'data'[DateB]
        );
        'data'[DateA] <= MAX ( 'calendar'[Date] )
            && 'data'[DateA]
                >= ( MAX ( 'calendar'[Date] ) - 180 )
            && 'data'[DateB] <= MAX ( 'calendar'[Date] )
            && 'data'[DateB]
                >= ( MAX ( 'calendar'[Date] ) - 180 )
    )
)

Check PBIX file attach.

[ChristianRHouen]

Hi Felix

Thanks for this

It seems as if I cant filter by selecting a given month.

For instance, in below shot I clicked december 2019 to see which dates were counted, but it doesn't affect the RHS table except for changing the count in all rows.

I would expect to only see the 43 cases in this incident.

[MFelix]

Hi @ChristianRHouen ,

This is related with the way measures work.etc)

Measures work with context so based on the information you provided (filters, rows, slicers, etc.) in this case when you pick up all the data from data table without placing any information from the calendar date the measure picks up the maximum values (31/12/2019) so all the rows within that period return the 43.

Do you want to have the calculation made for each of the rows of data? What is exactly your purpose.

As refered depending on the way we do calculations in dax the results are different.

[ChristianRHouen]

For transparancy, the user needs the ability to dive into what data is being counted. This is in order to provide multiple levels of detail, as each row has a lot more data attached to it.

Do you think its possible to do?

[MFelix]

Hi  @ChristianRHouen 

If you add the calendar date from the calendar date to your visualization you will get the information you need. But this off course depends on how you want to present things.

If you are using a table or a matri visualization you can add the date and just hide the column of the calendar date, if you are using a bar chart you need to use the calendar date as your x-axis.

[v-xuding-msft]

Hi @ChristianRHouen ,

Did you try the way that @MFelix  suggested? Have you resolved it? If yes, please accept the helpful answer as solution. You also can share your own solution. More people who have the same request will benefit here.  If you still have questions, please feel free to ask us.

[ChristianRHouen]

I've made a mockdata file (embedded on WeTransfer link, as I couldn't see how to embed pbi files here).

https://wetransfer.com/downloads/4185f577e14fcfffa52e12d0150ec2b220200423133721/2ba8a4c9149d265f7355...

As you can see, I don't get any data at all when using your formula. Perhaps I'm using it wrongly?