Retrieve the Lastest Available Allocation when Month is Zero

[swwong1]

Hi All

I have a table with a list of projects that we log every month. The person allocated to them can change every month so we have monthly data (StarterFlag and FinisherFlag are Custom Columns). I would like to retrieve the latest allocation month where it's not zero but struggling on the date context - tried LASTNONBLANK

For example in Project RG, April would take the allocation as 1 under salesperson Andy but for May, I also want to take allocation as 1 under salesperson Andy even though the raw data is 0 (latest month allocation).

PBI File 

Thanks All!

[v-saisrao-msft]

Hi @swwong1,

We haven’t heard back from you in a while. If your issue has been resolved, please mark the relevant response as the solution to help fellow community members.
If you still need support, Please let us know if you still need assistance.

Thank you.

[v-saisrao-msft]

Hi @swwong1,

I hope you had a chance to review the solution shared earlier. If it addressed your question, please consider accepting it as the solution it helps others find answers more quickly.
If you're still facing the issue, feel free to reply, and we’ll be happy to assist further.

Thank you

[v-saisrao-msft · Accepted solution]

Hi @swwong1,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @Irwan @Ashish_Excel  for your input on this issue. 
After thoroughly reviewing the details you provided, I was able to reproduce the scenario.

Dax Measure:

Final Allocation Excluding Novak = 
CALCULATE(
    SUMX(
        SUMMARIZE(
            FILTER(
                fTransaction,
                fTransaction[Salesperson] <> "Novak"
            ),
            fTransaction[Salesperson],
            fTransaction[Project],
            'dDate'[Month Name],
            "Alloc", MAX(fTransaction[Allocation])
        ),
        [Alloc]
    )
)

Output: 

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly. 
 
Thank you.

[v-saisrao-msft]

Hi @swwong1,
I wanted to check if you had the opportunity to review the information provided by @Irwan . Please feel free to contact us if you have any further questions. If thge response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[Ashish_Excel]

Hi,

In the same PBI file, please show the expected result very clearly.

[swwong1]

Thanks for looking into this. Below is the result I would want (I can't do this in PBI file as I can't solve the calculation).

For normal cases, the measure would be the SUM(fTransactions[Allocation])

However if that month is 0, I would want it to take the last month with Allocation

[Ashish_Excel]

Hi,

This measure pattern should help.  My assumption is that the month in the visual has been dragged from the Calendar table.

Measure = SUM(fTransactions[Allocation])

Measure 1 = calculate([Measure],calculatetable(lastnonblank(calendar[date],calculate([measure])),datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

Hope this helps.

[Irwan]

hello @swwong1 

i might be misunderstood but seems you want to get running total from your description.

create a new measure with following DAX.

Running Total =
SUMX(
    FILTER(
        ALL('fTransaction'),
        'fTransaction'[TrxDate]<=MAX('fTransaction'[TrxDate])&&
        'fTransaction'[Project]=SELECTEDVALUE('fTransaction'[Project])&&
        'fTransaction'[Salesperson]=SELECTEDVALUE('fTransaction'[Salesperson])
    ),
    'fTransaction'[Allocation]
)

Hope this will help.

Thank you.

[swwong1]

Thanks for looking into this. Below is the result I would want. What I need is the monthly allocation by project and salesperson. This can change every month. 

For normal cases, the measure would be the SUM(fTransactions[Allocation])

However if that month is 0, I would want it to take the last month with Allocation (refer to highlighted cell)

For May, the Allocation is 0, therefore it would took the Allocation from April - if April was also 0, it should take from March.

Thanks!

[Irwan]

hello @swwong1 

i dont know your whole original table looks like, but i would do something like below.

1. change the SUMX in previous DAX into MAXX

2. plot into matrix visual instead of table visual

feel free to try @Ashish_Excel , see which is satisfied your requirement.

Hope this will help.

Thank you.