getting a max of a measure and keeping it fix monthly

[ljx0648]

Hi guys,

I have two tables already connected in powerbi (using both "date" column in the table)

Last date of previous fiscal refers to the last date of the previous fiscal year (New fiscal starts Nov 01 every year)

I got the result for the asset last date of previous fiscal below using a measure (Last FY Measure)

My question here will be: I am creating a dashboard using the result and tables above.

I am trying to first create a measure to get the MAX of asset prvious year (highlighted max column) then another measure to get the final result of the Percent column. (There will be more than one MAX value when the Date is 30 Nov, 2020 and 30 Nov, 2021 as fiscal year increases)

I will have two filters: Date and Customer. For the final product, if I pick the filter 30 Nov, 2019 (Date) and A (Customer) the result I would like to get is 3 divded by 11. 

Any tips would be helpful.

Thank you!

[v-jianboli-msft · Accepted solution]

Hi @ljx0648 ,

Please try:

MAX Of Previous Fiscal = MAXX(FILTER(SUMMARIZE(ALL(Asset),Asset[Date],'Asset'[Customer],"Value",SUM(Asset[Current Asset])),[Date]=MAX('Calendar'[Last Date of previous fiscal])),[Value])

Asset Last Date Of Previous Fiscal = CALCULATE(SUM(Asset[Current Asset]),FILTER(ALL('Calendar'),[Date]=MAX('Calendar'[Last Date of previous fiscal])))

Percent = DIVIDE([Asset Last Date Of Previous Fiscal],[MAX Of Previous Fiscal])

Final output:

Best Regards,

Jianbo Li

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

[ljx0648]

Works like a charm! Thank you so much my friend