cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Fill blank with last month's figure

Hi ALL,

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.

1 ACCEPTED SOLUTION
Community Support

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.

6 REPLIES 6
Community Support

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.

Helper I

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!

Helper I

Hi Rico,

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

Super User

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-...

Helper I

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!

Helper I

Thanks Ibendlin

i've updated the table

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors