Forum Discussion

Oharoonchipz's avatar
Oharoonchipz
Frequent Visitor
4 years ago
Solved

Waterfall Chart- MTD Comparison

Hi All,

 

Need some help with my waterfall chart. As a novice in Power BI, I've been trying to bring myself up to speed via reading different tutorials and YT resources followed by lots of practice but the time based functions seem to be the most challenging for me.

 

Problem: I have one data set in which I need to show how much Bundle Data Traffic increased from Feb'22 to Mar'22. Currently, my waterfall chart shows the comparison of whole Feb average vs the MTD average for Mar. Ideally i'd like the waterfall chart to show the same period comparison (e.g. from 14th of Feb till 14 of Mar). Is there any way to achieving this?

 

Thanks.

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  Oharoonchipz ,

    You can change dax to the following form:

    CM Data Traffic =
VAR _lastActualValue =
    MAX ( Traffic[Date] )
RETURN
    IF (
        SELECTEDVALUE ( Dates[Date] ) <= _lastActualValue,
        CALCULATE (
            'Data Traffic Measures'[Daily Avg Data Traffic],
            FILTER (
                ALL ( Dates ),
                Dates[Date]
                    >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) )
                    && Dates[Date] <= TODAY ()
            )
        )
    )

    If I have misunderstood your meaning, please provide your desired output and your pbix file without privacy information.

     

    Best Regards,

    Liu Yang

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

3 Replies

  • mh2587's avatar
    mh2587
    Super User
    4 years ago

     Traffic = CALCULATE (
                Sum(Traffic),
                DATEADD ( 'Date'[Date], -1, MONTH))

    • Oharoonchipz's avatar
      Oharoonchipz
      Frequent Visitor
      4 years ago

      I have this already for my CM Traffic

       

      CM Data Traffic =
      //Avg Traffic MTD Formula
      var _lastActualValue= Max(Traffic[Date])
      return

      IF(
      SELECTEDVALUE(Dates[Date]) <= _lastActualValue,
      CALCULATE('Data Traffic Measures'[Daily Avg Data Traffic],
      DATESMTD(Dates[Date]))
      )
       
      For the ending point (i.e. March mtd), the numbers are fine but since it's CM Traffic, the starting number for Feb is the overall monthly average as opposed to the monthly average till 14th Feb. That's the current issue i'm trying to resolve
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Oharoonchipz ,

    You can change dax to the following form:

    CM Data Traffic =
VAR _lastActualValue =
    MAX ( Traffic[Date] )
RETURN
    IF (
        SELECTEDVALUE ( Dates[Date] ) <= _lastActualValue,
        CALCULATE (
            'Data Traffic Measures'[Daily Avg Data Traffic],
            FILTER (
                ALL ( Dates ),
                Dates[Date]
                    >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) )
                    && Dates[Date] <= TODAY ()
            )
        )
    )

    If I have misunderstood your meaning, please provide your desired output and your pbix file without privacy information.

     

    Best Regards,

    Liu Yang

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