Previous date's value

[Ajaal]

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 ?

[Ajaal]

Last Date-Copy 

[Ashish_Mathur]

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.

[v-kongfanf-msft]

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]

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

[Ajaal]

OK...my data looks like something below

Site	Producer	Date	Prodction
Site1	1_A	1/1/2024	25
Site1	1_B	1/1/2024	30
Site1	1_C	1/1/2024	30
Site1	1_D	1/1/2024	44
Site2	2_A	1/1/2024	112
Site2	2_B	1/1/2024	93
Site2	2_C	1/1/2024	29
Site3	3_A	1/1/2024	300
Site3	3_B	1/1/2024	222
Site4	4_A	1/1/2024	405
Site5	5_A	1/1/2024	200
Site5	5_B	1/1/2024	330
Site5	5_C	1/1/2024	110
Site1	1_A	2/1/2024	52
Site1	1_B	2/1/2024	13
Site1	1_C	2/1/2024	2
Site1	1_D	2/1/2024	0
Site2	2_A	2/1/2024	100
Site2	2_B	2/1/2024	90
Site2	2_C	2/1/2024	35
Site3	3_A	2/1/2024	200
Site3	3_B	2/1/2024	66
Site4	4_A	2/1/2024	279
Site5	5_A	2/1/2024	150
Site5	5_B	2/1/2024	300
Site5	5_C	2/1/2024	12
Site1	1_A	3/1/2024	44
Site1	1_B	3/1/2024	40
Site1	1_C	3/1/2024	31
Site1	1_D	3/1/2024	13
Site2	2_A	3/1/2024	107
Site2	2_B	3/1/2024	90
Site2	2_C	3/1/2024	46
Site3	3_A	3/1/2024	213
Site3	3_B	3/1/2024	21
Site4	4_A	3/1/2024	300
Site5	5_A	3/1/2024	150
Site5	5_B	3/1/2024	390
Site5	5_C	3/1/2024	22

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

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Ajaal]

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]

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

[Ajaal]

would you Please,

 write the two measures

   Date=?

   Last available Date=?

Thnx...

[Ashish_Mathur]

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

[Uzi2019]

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