Forum Discussion

swwong1's avatar
swwong1
Helper III
1 year ago
Solved

Retrieve the Lastest Available Allocation when Month is Zero

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...
Need Help
  • v-saisrao-msft's avatar
    v-saisrao-msft
    1 year ago

    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.

Irwan's avatar
Irwan
Super User
1 year ago

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's avatar
swwong1
Helper III
1 year ago

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's avatar
    Irwan
    Super User
    1 year ago

    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.