cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

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 ?

11 REPLIES 11
Frequent Visitor
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi  @Ajaal ,

You can try formula like below:

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

Best Regards,

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

Frequent Visitor
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

returns
3/1/2024
Frequent Visitor

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

Super User

Hi,

PBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

write the two measures

Date=?

Last available Date=?

Thnx...

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors