Find the last period with data

[rpinxt]

So I have thru an autocalendar periods/monts/monthnr etc.

Now I want to find automatically the last period that has data.

In my source at the moment this is 09-2022.

However when I uses MAX([Period]) I get 12-2022.

But there is no data (yet) for months above 09-2022.

Is there a way to configure the MAX so that it only looks for a period that also has data?

[v-yiruan-msft · Accepted solution]

Hi @rpinxt ,

You can create a measure as below to get it:

Sum of sales = SUM('Table'[sale])

Measure = CALCULATE ( MAX ( 'Table'[Period] ), FILTER ( 'Table', [Sum of sales] > 0 ) )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Ashish_Mathur]

Hi,

Create a Calendar Table.  Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  Write these measures

Total = sum(Data[Sales])

Last date of sale = calculate(max(Calendar[Date]),filter(values(Calendar[Date]),[Total>0]))

Hope this helps.

[rpinxt]

Thanks @v-yiruan-msft but somehow it is not working for me:

LastPeriod = CALCULATE(MAX(Dim_Date[Period]),FILTER('A********erge','A********erge'[Quantity] > 0 ))

This now even returns 12-2021......🙄

Actually  what I was trying to do I now fixed it with relative date (should have thought earlier about that! 🤦‍♂️)

However the question is still there....how could I achieve that the formula gives me the latest date with data.

And not 12-2022 which is the latest date in autocalendar and also not 12-2021.

But in this case 09-2022.

[v-yiruan-msft]

Hi @rpinxt ,

In order to make troubleshooting and give you a suitable solution quickly, could you please provide some sample data in your table 'A********erge' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[v-yiruan-msft · Accepted solution]

Hi @rpinxt ,

You can create a measure as below to get it:

Sum of sales = SUM('Table'[sale])

Measure = CALCULATE ( MAX ( 'Table'[Period] ), FILTER ( 'Table', [Sum of sales] > 0 ) )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[rpinxt · Accepted solution]

Thanks @v-yiruan-msft with your example I was able to make it work.

However had to make some changes as the period format caused it to not work:

"LastMonth = FORMAT(CALCULATE(MAX(Dim_Date[Date]),FILTER('A***********e',ABS(SUM('A**********e'[Quantity]))>0)),

"MM-YYYY")"

If I do a max on my Period (MM-YYYY) from the autocalendar I get wrong period.

However when I used the date field it gave me a date in period 9 of year 2022 which was correct.

So now I used date to get the correct month and then used format again to turn it into the Periode format I am using.

Thanks.