Powerbi formula Issue

[tsimon21]

Total Volume / Day - Combined vs Last 4 Days of Week - % =

DIVIDE([Total Volume - Combined - Last Date]

 - [Total Volume / Day - Combined - Last 4 Days of Week], [Total Volume / Day - Combined - Last 4 Days of Week], 0)

I am trying to get this forula to work for viewing yesterday's date vs the prior 4 same days in prior 4 weeks. For example this Friday vs the previous 4 fridays. I am trying to see how the day is trending vs prior weeks, but the data consistently comes back blank. I believe the issue lies with the date filtering.

[lbendlin]

I don't see in your formula where you subtract 1,8,15,22,29 from TODAY().

[Anonymous]

Hi @tsimon21 ,

Thanks for the reply from @lbendlin , please allow me to provide another insight.

You need to use the date function correctly, refer to the following formula:

result_ =
VAR cur_ =
    CALCULATE (
        SUM ( 'Table'[Total Volume] ),
        DATESBETWEEN ( 'Table'[Date], TODAY (), TODAY () )
    )
VAR sel_ =
    SELECTEDVALUE ( slicer[Week] )
VAR last_sel_week =
    CALCULATE (
        SUM ( 'Table'[Total Volume] ),
        DATESBETWEEN ( 'Table'[Date], TODAY () - sel_, TODAY () - sel_ )
    )
RETURN
    DIVIDE ( cur_ - last_sel_week, last_sel_week )

Best Regards,
Adamk Kong

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

[tsimon21]

This gets me close, but I can only view vs 1 prior week. I need the combined past 4 fridays vs the current. For example: 3/22 compared to Average of (3/15, 3/8, 3/1, 2/23), and this needs to be dynamic so it moves with each new day

[tsimon21]

stg_Day_of_Week =

SELECTCOLUMNS(

FILTER(dim_Calendar,

dim_Calendar[Date] > TODAY() - 31 && dim_Calendar[Date] < TODAY() - 6 &&

dim_Calendar[day of week] = FORMAT ( TODAY() - 1, "ddd" )),

"Date", dim_Calendar[Date], "day of week", dim_Calendar[day of week]) current formula that is coming back as Scalar

[Anonymous · Accepted solution]

Hi @tsimon21 ,

Create calculated colunm and modify formual like below:

week = WEEKDAY('Table'[Date],2)

result_ =
VAR cur_ =
    CALCULATE (
        SUM ( 'Table'[Total Volume] ),
        DATESBETWEEN ( 'Table'[Date], TODAY (), TODAY () )
    )
VAR sel_ =
    SELECTEDVALUE ( slicer[Week] )
VAR week_ =
    WEEKDAY ( TODAY () - sel_, 2 )
VAR last_sel_week =
    CALCULATE (
        SUM ( 'Table'[Total Volume] ),
        DATESBETWEEN ( 'Table'[Date], TODAY () - sel_, TODAY () - sel_ ),
        FILTER ( 'Table', 'Table'[week] = week_ )
    )
RETURN
    DIVIDE ( cur_ - last_sel_week, last_sel_week )

Best Regards,
Adamk Kong

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