Calculating status totals for current and future months with no date identifier in data

[GeorgeES]

Good evening,

As a way of background - I currently have a process where I manually produce a table which contains counts for items in a certain status for each month. e.g. 

2022 - Jan | 14
2022 - Feb | 12
2022 - Mar | 32

The reason it was produced manually is there is nothing in the data which advises which month the particular row was produced in - created or similar. The data produced is a snap shot of the current moment, with no way of calculating the total looking backwards. We only know that there was 12 items in Feb because the data was reviewed in Feb and 12 items were manually counted. Here in lies the challenge. 

I'm moving this process to PowerBI and was wondering if there is any way to do this without having the date / monthly row identifier? I'd also need a solution that calculates the total for future months so the process is automated. I've attempted to do this with a date table but with no way of creating a relationship, I wasn't able to move forward. I was also thinking there may be a function which would facilitate this but I wasn't able to track one down. 

In an ideal world, I was re-engineer the data to give that date / month identifier but sadly I'm not able to do so. Any suggestions for this would be greatly appreciated and I am able to implement any solution within Power Automate or PowerBI itself. 

I appreciate that I haven't provided any sample data and will gladly do so if my description is not suitable. 

Kind regards,

George

[amitchandak]

@GeorgeES , I would suggest having a date using month.

or you can use Month Rank on YYYYMM format to get Time Intelligence

But have a separate table for month year

Month Rank = RANKX(all('Date'),'Date'[Month Year],,ASC,Dense)

Measure

This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

Next Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])+1))

DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[GeorgeES]

Hi @amitchandak ,

Thanks for providing your help on this. Is it possible to do the same but for row count? I've given it a go but I'm getting blank each time I try - I've tried count, distinctcount and countrows with no luck. 

Also to confirm that 

This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))

will give me the data for this month?

I've attached a copy of my date table to make sure there are no issues there.

https://drive.google.com/file/d/1chyb2JJhgeSAd18w6mrL71Negu8Q-9Yh/view?usp=sharing

Thanks again for your help.