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 - 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.
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.

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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

  • 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