Calculated Column

[Anonymous]

I have been tasked to create a measure that will show total revenue - 2 week revenue and another for total revenue and 4 weeks ago. I have these measures created already but the problem is they want these measures to popluate with a date slicer and I don't know how to do that can you help me.

[Anonymous · Accepted solution]

Hi @Anonymous 

Thank you very much sevenhills and Power2G for your prompt reply.

For your question, here is the method I provided:

Here's some dummy data

"Sales"

Ensure you have a Date table in your model.

DateTable = CALENDAR(MIN('Sales'[Date]), MAX('Sales'[Date]))

Create measures.

Revenue2WeeksAgo = 
VAR DataWeekNUM = WEEKNUM(MAX('DateTable'[Date]))
RETURN
CALCULATE(
    SUM('Sales'[Revenue]),
    FILTER(
        ALL('Sales'),
        WEEKNUM('Sales'[Date]) < DataWeekNUM
        &&
        WEEKNUM('Sales'[Date]) >= DataWeekNUM - 2
    )
)

Revenue4WeeksAgo = 
VAR DataWeekNUM = WEEKNUM(MAX('DateTable'[Date]))
RETURN
CALCULATE(
    SUM('Sales'[Revenue]),
    FILTER(
        ALL('Sales'),
        WEEKNUM('Sales'[Date]) < DataWeekNUM
        &&
        WEEKNUM('Sales'[Date]) >= DataWeekNUM - 4
    )
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sevenhills]

I think your post title says Calculated Column and you are asking for measures! This is minor point.

Let us go through your requirements

1. You already have [Total Revenue], [Total Revenue - 2 W], [Total Revenue - 4 W] measures

2. You want to adjust based on a date slicer.

You can do these:

1. Create a slicer using your date table 

2. Adjust the measures, let u do one and then you can do the other similar way by adjusting -14 as -28.

[Total Revenue - 2 W] =
VAR _SelDate =
    IF (
        ISFILTERED ( 'DateTable'[Date] ),
        MAX( 'DateTable'[Date] ),
         BLANK()  
    )
RETURN  IF( ISBLANK( SUM(Sales[Revenue])  ) || ISBLANK( _SelDate ) || NOT HASONEVALUE('DateTable'[Date]), BLANK(), 
    CALCULATE (
        SUM ( 'Sales'[Revenue] ),
        DATESINPERIOD(  Sales[Date], _SelDate, -14, DAY )
    )
)
        
 

3. For testing add 2 dummy measures and remove after your testing

Total Revenue - 2 W - Min Date = 
VAR _SelDate =
    IF (
        ISFILTERED ( 'DateTable'[Date] ),
        MAX( 'DateTable'[Date] ),
        BLANK()  
    )
RETURN  minx(  DATESINPERIOD(  'DateTable'[Date], _SelDate, -14, DAY ) , 'DateTable'[Date])

Total Revenue - 2 W - Max Date = 
VAR _SelDate =
    IF (
        ISFILTERED ( 'DateTable'[Date] ),
        MAX( 'DateTable'[Date] ),
        BLANK()  
    )
RETURN  maxx(  DATESINPERIOD(  'DateTable'[Date], _SelDate, -14, DAY ) , 'DateTable'[Date])

Sample from adventure works:

 Hope it helps!

[Power2G]

To beging, make sure you have a proper date table in your data model and create relationships between your Sales[Date] and the Date table.

First measure

TotalRevenue = SUM(Sales[Revenue])

Second measure

RevenueLast2Weeks = 
CALCULATE(
    [TotalRevenue],
    DATESINPERIOD(
        Sales[Date],
        MAX(Sales[Date]),
        -2,
        WEEK
    )
)

Third measure

RevenueLast4Weeks = 
CALCULATE(
    [TotalRevenue],
    DATESINPERIOD(
        Sales[Date],
        MAX(Sales[Date]),
        -4,
        WEEK
    )
)