Forum Discussion

tsimon21's avatar
tsimon21
Regular Visitor
2 years ago
Solved

Powerbi formula Issue

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 - L...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

tsimon21's avatar
tsimon21
Regular Visitor
2 years ago

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's avatar
tsimon21
Regular Visitor
2 years ago
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