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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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'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
  • Anonymous's avatar
    Anonymous
    Not applicable
    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.