Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
lucasneedhelp
Helper I
Helper I

Fill blank with last month's figure

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 IDFcst Change DateCurrent ForecastDept GroupRes Code
8322119/11/202134800Venue HireMECSPACERENT
832219/06/20220Venue HireMCCSPACERENT
832219/06/202250940Venue HireMECSPACERENT
832215/12/202233960Venue HireMECSPACERENT
8322113/01/2023300Event ServiceEVSVELECFORE
8322113/01/2023582.56Event ServiceEVSVECRFC
8322113/01/2023711.7Event ServiceEVSVBAYFORE
8322113/01/202370000CateringFBFORECASTS
8322116/01/20239677TSDAVFORECAST
832211/02/20234770Venue HireMCCSPACERENT
8322121/02/202310472TSDAVFORECAST
832214/05/20238697.36TSDAVFORECAST
832214/05/20238687.36TSDAVFORECAST
832214/05/20238022.36TSDAVFORECAST
8322122/05/20238867.36TSDAVFORECAST

 

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

lucasneedhelp_1-1685687863619.png

 

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

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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.

vrzhoumsft_0-1685956936741.png

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.

 

View solution in original post

6 REPLIES 6
v-rzhou-msft
Community Support
Community Support

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.

vrzhoumsft_0-1685956936741.png

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.

 

Hi Rico,

i've cleaned my data like below 

Event IDRes CodeDateCurrent Forecast
49327AV31/07/202080000
49327AV31/08/2020 
49327AV30/09/2020 
49327AV31/10/2020 
49327BEV30/11/202082488.84
49327BEV31/12/2020 
49327BEV31/01/2021 
49327BEV28/02/202175000
49327BEV31/03/2021 
49327BEV30/04/2021 
49327BEV31/05/2021 
56785AV30/06/202282488.84
56785AV31/07/2022 
56785AV31/08/2022 
56785AV30/09/202286210.93
56785AV31/10/2022 
56785BEV30/11/202299171.04
56785BEV31/12/2022 
56785BEV31/01/2023 
56785BEV28/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!

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

Hi Ibendin,

sorry to bother you again.

i've cleaned my data like below 

Event IDRes CodeDateCurrent Forecast
49327AV31/07/202080000
49327AV31/08/2020 
49327AV30/09/2020 
49327AV31/10/2020 
49327BEV30/11/202082488.84
49327BEV31/12/2020 
49327BEV31/01/2021 
49327BEV28/02/202175000
49327BEV31/03/2021 
49327BEV30/04/2021 
49327BEV31/05/2021 
56785AV30/06/202282488.84
56785AV31/07/2022 
56785AV31/08/2022 
56785AV30/09/202286210.93
56785AV31/10/2022 
56785BEV30/11/202299171.04
56785BEV31/12/2022 
56785BEV31/01/2023 
56785BEV28/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!

Thanks Ibendlin

i've updated the table 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors