Fill blank with last month's figure

[lucasneedhelp]

Hi ALL,

I've been stuck here for 3 days and please help. Really appreciated!

The final goal is to have a table of forecast amount by Endofmonth (Forecast Date) and by Dept Group. when there's a amount for the Dept Group in a particular month, use the amount. otherwise use last nonblankvalue. Dept Group add up all Res Code changes within the depart group.

Here's a sample data

Event ID	Fcst Change Date	Current Forecast	Dept Group	Res Code
83221	19/11/2021	34800	Venue Hire	MECSPACERENT
83221	9/06/2022	0	Venue Hire	MCCSPACERENT
83221	9/06/2022	50940	Venue Hire	MECSPACERENT
83221	5/12/2022	33960	Venue Hire	MECSPACERENT
83221	13/01/2023	300	Event Service	EVSVELECFORE
83221	13/01/2023	582.56	Event Service	EVSVECRFC
83221	13/01/2023	711.7	Event Service	EVSVBAYFORE
83221	13/01/2023	70000	Catering	FBFORECASTS
83221	16/01/2023	9677	TSD	AVFORECAST
83221	1/02/2023	4770	Venue Hire	MCCSPACERENT
83221	21/02/2023	10472	TSD	AVFORECAST
83221	4/05/2023	8697.36	TSD	AVFORECAST
83221	4/05/2023	8687.36	TSD	AVFORECAST
83221	4/05/2023	8022.36	TSD	AVFORECAST
83221	22/05/2023	8867.36	TSD	AVFORECAST

And here's my desired results. it needs to list every month between first forecast month and last forecast month. 

Could someone please help with Dax? Thank you so very much!

[v-rzhou-msft · Accepted solution]

Hi @lucasneedhelp ,

I think you can create measures to achieve your goal.

Catering = 
CALCULATE (
    SUM ( 'Table'[Current Forecast] ),
    FILTER (
        ALL('Table'),
        'Table'[Dept Group] = "Catering"
            && 'Table'[Fcst Change Date] <= MAX ( 'Calendar'[Date] )
    )
)

Event Service = 
CALCULATE (
    SUM ( 'Table'[Current Forecast] ),
    FILTER (
        ALL('Table'),
        'Table'[Dept Group] = "Event Service"
            && 'Table'[Fcst Change Date] <= MAX ( 'Calendar'[Date] )
    )
)

TSD = 
VAR _LASTDATE =
    CALCULATE (
        MAX ( 'Table'[Fcst Change Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Dept Group] = "TSD"
                && 'Table'[Fcst Change Date] <= MAX ( 'Calendar'[Date] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Current Forecast] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Dept Group] = "TSD"
                && 'Table'[Fcst Change Date] = _LASTDATE
        )
    )

Venue Hire = 
VAR _LASTDATE =
    CALCULATE (
        MAX ( 'Table'[Fcst Change Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Dept Group] = "Venue Hire"
                && 'Table'[Fcst Change Date] <= MAX ( 'Calendar'[Date] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Current Forecast] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Dept Group] = "Venue Hire"
                && 'Table'[Fcst Change Date] = _LASTDATE
        )
    )

Result is as below.

According to your screenshot I see the Venue Hire from Feb-2023 to May-2023 is 4770 + 33960. However the above Venue Hire will not sum.

Best Regards,
Rico Zhou

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

[lucasneedhelp]

Hi Rico,

i've cleaned my data like below 

Event ID	Res Code	Date	Current Forecast
49327	AV	31/07/2020	80000
49327	AV	31/08/2020
49327	AV	30/09/2020
49327	AV	31/10/2020
49327	BEV	30/11/2020	82488.84
49327	BEV	31/12/2020
49327	BEV	31/01/2021
49327	BEV	28/02/2021	75000
49327	BEV	31/03/2021
49327	BEV	30/04/2021
49327	BEV	31/05/2021
56785	AV	30/06/2022	82488.84
56785	AV	31/07/2022
56785	AV	31/08/2022
56785	AV	30/09/2022	86210.93
56785	AV	31/10/2022
56785	BEV	30/11/2022	99171.04
56785	BEV	31/12/2022
56785	BEV	31/01/2023
56785	BEV	28/02/2023

are you able to help with formular to fill blank with last month's number by event id by res code? somehow my formular doesn't give me the desired result but very close

=
var vLastNonBlankDate=
CALCULATE(MAX('Mthly Calendar to FY25'[Date]),
FILTER(ALL('test grouped record'),
'test grouped record'[End of Month]<=MAX('Mthly Calendar to FY25'[Date]) &&
'test grouped record'[EOM Forecast.Current Forecast]<>0
)
)
var vLastNonBlankFcst=
CALCULATE(AVERAGE('test grouped record'[EOM Forecast.Current Forecast]),FILTER(ALL('Mthly Calendar to FY25'),'Mthly Calendar to FY25'[Date]=vLastNonBlankDate))
return
vLastNonBlankFcst

much appreciated!

[lucasneedhelp]

Hi Rico,

thanks for your help.

I'm thinking to use similar logic to give me last forecast for each resource by month first and somehow sum up by department. haven't practised yet. but will give it a go 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[lucasneedhelp]

Hi Ibendin,

sorry to bother you again.

i've cleaned my data like below 

Event ID	Res Code	Date	Current Forecast
49327	AV	31/07/2020	80000
49327	AV	31/08/2020
49327	AV	30/09/2020
49327	AV	31/10/2020
49327	BEV	30/11/2020	82488.84
49327	BEV	31/12/2020
49327	BEV	31/01/2021
49327	BEV	28/02/2021	75000
49327	BEV	31/03/2021
49327	BEV	30/04/2021
49327	BEV	31/05/2021
56785	AV	30/06/2022	82488.84
56785	AV	31/07/2022
56785	AV	31/08/2022
56785	AV	30/09/2022	86210.93
56785	AV	31/10/2022
56785	BEV	30/11/2022	99171.04
56785	BEV	31/12/2022
56785	BEV	31/01/2023
56785	BEV	28/02/2023

are you able to help with formular to fill blank with last month's number by event id by res code? somehow my formular doesn't give me the desired result but very close

=
var vLastNonBlankDate=
CALCULATE(MAX('Mthly Calendar to FY25'[Date]),
FILTER(ALL('test grouped record'),
'test grouped record'[End of Month]<=MAX('Mthly Calendar to FY25'[Date]) &&
'test grouped record'[EOM Forecast.Current Forecast]<>0
)
)
var vLastNonBlankFcst=
CALCULATE(AVERAGE('test grouped record'[EOM Forecast.Current Forecast]),FILTER(ALL('Mthly Calendar to FY25'),'Mthly Calendar to FY25'[Date]=vLastNonBlankDate))
return
vLastNonBlankFcst

much appreciated!

[lucasneedhelp]

Thanks Ibendlin

i've updated the table