Identify Actuals and Mark them with an A

[Invesco]

Hi Experts

The Financial Year runs from 01/04 to 31/03. 

I need to remove the actuals from my FACT table (see sample data) - In Column Dataset - i have Forecast for 4 Periods

1. Forecast - P01

2. Forecast - P02

3. Forecast - P03 and

4. Forecast - P04

Where we have Forecast - P01 - The Month of Apr is Actuals and all other months for the year are Forecasts. 

Where we have Forecast - P02 - The Month of Apr  May is Actuals and all other months for the year are Forecasts. 

Where we have Forecast - P03 - The Month of Apr, May  June  is Actuals and all other months for the year are Forecasts. 

Where we have Forecast - P04 - The Month of Apr, Ma, June  July is Actuals and all other months for the year are Forecasts. 

The link to the sample file is a sub-set of the bigger picture my master FACT table has 5.6m rows of data i need to be able to create a new column in Power Query and mark the Actual "A" and Forecast "F" for any given FY Period (FY runs from 01/04 to 31/03)

https://www.dropbox.com/scl/fi/rgkcj6pnajae7wfl1s9q9/Sample.pbix?rlkey=8a1etha8t13zbffggpco1csdy&st=... 

[v-denglli-msft · Accepted solution]

Hi @Invesco ,

The M code is as follows, 

 #"Add Custom" = Table.AddColumn(Source, "Status", each 
        if [Dataset] = "Forecast - P01" and  Date.Month([Date]) = 4 then "A" else 
        if [Dataset] = "Forecast - P02" and (Date.Month([Date])  = 4 or Date.Month([Date])  = 5) then "A" else 
        if [Dataset] = "Forecast - P03" and (Date.Month([Date])  = 4 or Date.Month([Date])  = 5 or Date.Month([Date])  = 6) then "A" else 
        if [Dataset] = "Forecast - P04" and (Date.Month([Date])  = 4 or Date.Month([Date])  = 5 or Date.Month([Date])  = 6 or Date.Month([Date]) = 7) then "A" else "F")

The result is as follows, hopefully it will meet your needs.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

[v-denglli-msft · Accepted solution]

Hi @Invesco ，

May I ask if you have gotten this issue resolved?
If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster .

Best Regards,
Dengliang Li

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

[v-denglli-msft · Accepted solution]

Hi @Invesco ,

The M code is as follows, 

 #"Add Custom" = Table.AddColumn(Source, "Status", each 
        if [Dataset] = "Forecast - P01" and  Date.Month([Date]) = 4 then "A" else 
        if [Dataset] = "Forecast - P02" and (Date.Month([Date])  = 4 or Date.Month([Date])  = 5) then "A" else 
        if [Dataset] = "Forecast - P03" and (Date.Month([Date])  = 4 or Date.Month([Date])  = 5 or Date.Month([Date])  = 6) then "A" else 
        if [Dataset] = "Forecast - P04" and (Date.Month([Date])  = 4 or Date.Month([Date])  = 5 or Date.Month([Date])  = 6 or Date.Month([Date]) = 7) then "A" else "F")

The result is as follows, hopefully it will meet your needs.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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