Forum Discussion

Ajaal's avatar
Ajaal
Helper I
2 years ago

Previous date's value

How do I create a measure that calculates and retrives the average of previous date values whenever the new data's corresponding values are added to the underlying dataset of the report? I wanna use this measure as threshold input in a guage visual ?

 

 

Need Help

11 Replies

  • Uzi2019's avatar
    Uzi2019
    Community Champion
    2 years ago

    hi Ajaal 
    Share some sample input and expected output. without proper data and output it is very difficult to answer your query.

     

     

  • Ajaal's avatar
    Ajaal
    Helper I
    2 years ago

    OK...my data looks like something below

    SiteProducerDateProdction
    Site11_A1/1/202425
    Site11_B1/1/202430
    Site11_C1/1/202430
    Site11_D1/1/202444
    Site22_A1/1/2024112
    Site22_B1/1/202493
    Site22_C1/1/202429
    Site33_A1/1/2024300
    Site33_B1/1/2024222
    Site44_A1/1/2024405
    Site55_A1/1/2024200
    Site55_B1/1/2024330
    Site55_C1/1/2024110
    Site11_A2/1/202452
    Site11_B2/1/202413
    Site11_C2/1/20242
    Site11_D2/1/20240
    Site22_A2/1/2024100
    Site22_B2/1/202490
    Site22_C2/1/202435
    Site33_A2/1/2024200
    Site33_B2/1/202466
    Site44_A2/1/2024279
    Site55_A2/1/2024150
    Site55_B2/1/2024300
    Site55_C2/1/202412
    Site11_A3/1/202444
    Site11_B3/1/202440
    Site11_C3/1/202431
    Site11_D3/1/202413
    Site22_A3/1/2024107
    Site22_B3/1/202490
    Site22_C3/1/202446
    Site33_A3/1/2024213
    Site33_B3/1/202421
    Site44_A3/1/2024300
    Site55_A3/1/2024150
    Site55_B3/1/2024390
    Site55_C3/1/202422

     

    I want to Create a measure that gives me an average of production for any filtered site above for  the prodction data added just before last added month, so for example if my last date is 3/1/2024 I want to a measure that calculates automatically the average prod of date 2/1/2024 and if data of next month was added "4/1/2024" the measure should automtically calculates the average of previuos month "3/1/2024"? so if I used any visual, I can have both  the average of this month which I can get when I apply the aggregate function of this month's data which I'm visualizing and the average of month before?

     

    Thnx

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  Ajaal ,

     

    You can try formula like below:

    M_ =
VAR LastAddedMonth =
    CALCULATE ( MAX ( 'Date'[Date] ), ALL ( 'Date' ) )
VAR LastMonth =
    MONTH ( LastAddedMonth ) - 1
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[Prodction] ),
        MONTH ( 'Table'[Date] ) = LastMonth
    )

     

    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.

    • Ajaal's avatar
      Ajaal
      Helper I
      2 years ago
      Thnx for replying Mr.Kong...
      The formula you've provided didn't work for me, I did not see the result of that measure M_ when adding it to a Card chart  (average value was blank)
       
      VAR LastMonth = MONTH ( LastAddedMonth ) - 1 ==>this part of your Formula does not seem to work for me, when I tried testing the value retrieved by this part as 
       
      M_ =
      VAR LastAddedMonth = CALCULATE ( MAX ( 'Date'[Date] ), ALL ( 'Date' ) )
      VAR LastMonth = MONTH ( LastAddedMonth ) - 1
      RETURN
         LastMonth
       
       returns 2 (not a Date Value)
       
      whereas the first part
      M_ =
      VAR LastAddedMonth = CALCULATE ( MAX ( 'Date'[Date] ), ALL ( 'Date' ) )
      VAR LastMonth = MONTH ( LastAddedMonth ) - 1
      RETURN
        LastAddedMonth
       
       returns
          3/1/2024
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      2 years ago

      Hi,

      Use this measure

      Production in latest previous month = CALCULATE(SUM(Data[Prodction]),CALCULATETABLE(LASTNONBLANK('Calendar'[Date],CALCULATE(sum(Data[Prodction]))),DATESBETWEEN('Calendar'[Date],minx(ALL('Calendar'),'Calendar'[Date]),[Last available date]-1)))

      Hope this helps.