Forum Discussion

swwong1's avatar
swwong1
Icon for Helper III rankHelper 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). 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!

 

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.

10 Replies

  • Irwan's avatar
    Irwan
    Icon for Super User rankSuper 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
      Icon for Helper III rankHelper 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
        Icon for Super User rankSuper 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.

  • Ashish_Excel's avatar
    Ashish_Excel
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

    Hi,

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

    • swwong1's avatar
      swwong1
      Icon for Helper III rankHelper III
      1 year ago

      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's avatar
        Ashish_Excel
        Icon for Solution Supplier rankSolution Supplier
        1 year ago

        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.

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    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.

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Icon for Community Support rankCommunity Support
    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.

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    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's avatar
    v-saisrao-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    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.