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

    • Ajaal's avatar
      Ajaal
      Helper I
      2 years ago

      would you Please,

       write the two measures

         Date=?

         Last available Date=?

       

      Thnx...

         

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        I have shared the file with you.  See the measures there.

    • Ajaal's avatar
      Ajaal
      Helper I
      2 years ago

      Mr. Ashish_Mathur Thank you for your reply...

       

       I've tested your solution (your powerbi report -Last date.PBIX) to make sure that it's working as expected.

      it seems that this measure is working fine if the added date is in the same year but if the year is changed then, it won't show the previous date "which is in the last yeas". I mean if the last prodction date is "for example" 1/1/2025, your report will show the prodction of any site in max month 1/1/2025 but the prodction of any site for the previous month which is gonna be 1/3/2024 won't be shown.? 

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        You are welcome.  Share the download link of the PBI file with the data that you have shown in the screenshot.