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

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.