Get previous total revenue based on rolling 1 week with datetime column

[RogerSteinberg]

Hi,

I have a slicer that groups dates into different periods.

For example one value in my slicer is called Period A which is the first week of february.

I created two measures that allows me to calculate the bounds for the previous week as such . Note that my transaction_date column is in datetime : 

PreviousWeek_MIN = MIN(AllPackages[transaction_date])-7

PreviousWeek_MAX = MAX(AllPackages[transaction_date])-7

Now I'd like my to create a measure that calculates the revenue with those parameters:

Revenue -1 Week =

VAR _summary =

SUMMARIZE(

FILTER(

ALL(AllPackages),

AllPackages[transaction_date]>=[PreviousWeek_MIN ] && AllPackages[transaction_date] <= [PreviousWeek_MAX]

),

AllPackages[transaction_date],"Rev",sum(AllPackages[revenue])

)

return

SUMX(

_summary,

[Rev]

)

WHen i put this measure in my table it is blank, I thought the ALL function would allow me to do that rolling 1 week revenue using my parameters ...

Desired results:

	Start	End	Start -1	End -1	Revenue  Current	Revenue -1 Week
Period A	2/10/2021 2:00 AM	2/12/2021 5:00 AM	2/3/2021 2:00 AM	2/5/2021 5:00 AM	$                    100.00	$                                          75.00
Period B	2/20/2021 2:00 AM	2/22/2021 5:00 AM	2/13/2021 2:00 AM	2/15/2021 5:00 AM	$                    150.00	$                                       100.00

Any thoughts?

[RogerSteinberg]

Please see my edited post ( i put my desired output ) .

 My start-1 and End-1 columns works fine... 

[v-jingzhang · Accepted solution]

Hi @RogerSteinberg 

You can use below measure to get the previous week total revenue. This should be easier.

Previous Total Revenue =
CALCULATE (
    SUM ( AllPackages[revenue] ),
    ALL ( AllPackages ),
    DATESBETWEEN (
        AllPackages[transaction_date],
        [PreviousWeek_MIN],
        [PreviousWeek_MAX]
    )
)

For your original measure, I use COUNTROWS(_summary) to return the number of rows of _summary table and gets the blank result. So the _summary variable table doesn't get the result correctly.

To use your measure, you can modify it into the following one. This should work. The reason is that when you use [PreviousWeek_MIN] and [PreviousWeek_MAX] directly in the FILTER() function, it will be calculated according to the filtered table's current row, so it will always get the blank value. 

Revenue -1 Week = 
VAR _maxDate = [PreviousWeek_MAX]
VAR _minDate = [PreviousWeek_MIN]
VAR _summary =
    SUMMARIZE (
        FILTER (
            ALL ( AllPackages ),
            AllPackages[transaction_date] >= _minDate && AllPackages[transaction_date] <= _maxDate
        ),
        AllPackages[transaction_date],
        "Rev", SUM ( AllPackages[revenue] )
    )
RETURN
    SUMX ( _summary, [Rev] )

Kindly let me know if this helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it. 

[selimovd]

Hello @RogerSteinberg ,

can you check the values of PreviousWeek_MIN and PreviousWeek_MAX?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[RogerSteinberg]

they're working fine. i had put the measures in the table initially to check their values and they work as intended