Dynamic calculation based on helper table and selected period

[saqwild]

Hi,

I have seen many posts and video related to my question, however nothing seems to be working as I expected, though I have tried several option and combinations. Hence seeking help herewith.

Scenario:

I have three tables as below.

1. Extract table, which contains my data, customer code, name, transaction reference, invoice data and amount

Account Code	Account Name	Trans Date	Trans Ref	Amount
150011	ABC Co	20/06/2024	2434578	6389
150011	ABC Co	23/07/2024	2434579	8876
150012	XYZ Co	23/01/2021	2166352	5566
150012	XYZ Co	15/04/2022	2226678	7744

2. Grid Table, containing matrix to calculate the amounts within threshold.

order	group	min	max	Percent
1	0-60	0	60	0%
2	61-90	61	90	5%
3	91-180	91	180	10%
4	181-360	181	360	50%
5	360+	361	99999999	100%

3. Selection Period Table, to show current or forecast calculation

Selected Period	Period Status
31/07/2024	Current
31/08/2024	Current +30
20/09/2024	Current +90

Above table I have generated with MAX of Transaction Date from extract table + EDATE(+x)

PeriodTable = 
        VAR BDLastDate = FORMAT(max('Extract'[Transaction Date]),"dd/mm/yyyy")
        VAR BDFCT30 = EDATE(BDLastDate,1)
        VAR BDFCT60 = EDATE(BDLastDate,2)
        VAR BDFCT90 = EDATE(BDLastDate,3)
RETURN
{(BDLastDate,"Current"),(BDFCT30,"Current +30"),(BDFCT60,"Current +60"),(BDFCT90,"Current +90")}

I have renamed the columns to "SelectedPeriod" and "Period Status". "Period Status" is my slicer filter 

Requirement:

I want to achieve the following.

• generate invoice age based on slicer filter, if slicer filter is "Current" then based on last date of transaction period, "Current +30" would be last transaction date +30 days and so on (as above)
• generate calculated amount within each Grid group, what will fall under 0-30, 0-60,0-90 and so on. This will change if the slicer filter changed from one to another. i.e. if slicer changes from "Current" to "Current +30", accordingly the grid, age and calculation will change.
• generate allowance provision based of Grid min max group divided by percent. i.e. if an amount is withing 0-60 days group then it will calculate a provision of 0%, 61-90 days will be amount multiplied by 5% and so on. This also needs to be changed as and when the slicer filter changes.

Trust above clarify the scenario.

Thank you in advance for the time and effort.

[gmsamborn]

Hi @saqwild 

Would the following measures help?

Value = SUM( 'Extract'[Amount] )


Invoice Age = 
VAR _SelectedPeriod = SELECTEDVALUE( 'Selection Period Table'[Selected Period] )
VAR _InvDt = MAX( 'Extract'[Trans Date] )
VAR _Result =
    DATEDIFF(
        _InvDt,
        _SelectedPeriod,
        DAY
    )
RETURN
    IF(
        HASONEVALUE( 'Extract'[Trans Ref] ),
        _Result,
        ""
    )


Aging Group = 
VAR _Age = [Invoice Age]
VAR _Result =
    CALCULATE(
        MAX( 'Buckets'[group] ),
        'Buckets'[min] <= _Age
            && 'Buckets'[max] >= _Age
    )
RETURN
    IF(
        HASONEVALUE( 'Extract'[Trans Ref] ),
        _Result,
        ""
    )


Pct = 
VAR _Age = [Invoice Age]
VAR _Result =
    CALCULATE(
        MAX( 'Buckets'[Percent] ),
        'Buckets'[min] <= _Age
            && 'Buckets'[max] >= _Age
    )
RETURN
    IF(
        HASONEVALUE( 'Extract'[Trans Ref] ),
        _Result,
        ""
    )


New Value = 
    SUMX(
        VALUES( 'Extract'[Trans Ref] ),
        [Value] * ( 1 + [Pct] )
    )

Let me know if you  have any questions.

saqwild.pbix

[saqwild]

@gmsamborn thank for your reply.

as i mentioned previsouly, my selected period is not the date but status

VAR _SelectedPeriod = SELECTEDVALUE( 'Selection Period Table'[Selected Period] )
this would be 
VAR _SelectedPeriod = SELECTEDVALUE( 'Selection Period Table'[Period Status] )

 at the same time i want to make the report dynamic based on Period status = Selected period.

I tried the above code an static date, all works fine excep, the date is hardcoded now and the total value of "New Value" is not correct.

[gmsamborn]

Hi @saqwild 

If both are in the same table, DAX can figure this out if you are using SELECTEDVALUE() since it looks at the whole row, not just the field in the slicer.

Check my pbix.

[saqwild]

thank you @gmsamborn, that works fine,

juse one issue now, when i try values by ageing groups, it always shows 100% bucket

thanks

[saqwild]

Any assistance on this is appreciated 

@Ahmedx 

@AlexisOlson 

@amitchandak 

@Ashish_Mathur 

@danextian 

@Fowmy 

[Sahir_Maharaj]

Hello @saqwild,

Can you please try the following approach:

1. Calculate the difference between the transaction date and the selected period

Invoice Age = 
VAR SelectedPeriod = MAX('Selection Period Table'[Selected Period])  -- Get the selected period from the slicer
RETURN
DATEDIFF('Extract'[Trans Date], SelectedPeriod, DAY)

2. Classify the invoices

Amount By Group = 
VAR InvoiceAge = [Invoice Age]  -- Use the invoice age calculated above
RETURN
CALCULATE(
    SUM('Extract'[Amount]),
    FILTER(
        'Grid Table',
        InvoiceAge >= 'Grid Table'[min] && InvoiceAge <= 'Grid Table'[max]
    )
)

3. Calculate the provision based on the thresholds and percentages

Provision Amount = 
VAR InvoiceAge = [Invoice Age]  -- Use the invoice age calculated earlier
VAR GroupPercent = 
    CALCULATE(
        MAX('Grid Table'[Percent]), 
        FILTER(
            'Grid Table',
            InvoiceAge >= 'Grid Table'[min] && InvoiceAge <= 'Grid Table'[max]
        )
    )
RETURN
[Amount By Group] * GroupPercent

Hope this helps!

[saqwild]

@Sahir_Maharaj 

I can not refer the column name directly in the measure hence i tried the following 

Invoice Age = 
VAR _CurrentPeriod = EOMONTH(MAX('Extract'[Transaction Date]),0)
VAR _ListDates = SELECTEDVALUE('Extract'[Transaction Date])
VAR _CurrentAge = DATEDIFF(_ListDates, _CurrentPeriod, DAY)

RETURN _CurrentAge

 but its giving the weired numebrs, not matching when i calculate manually.

[saqwild]

Thank you Sahir,

As mentiond above, my slicer is not the date but "Period Status", so i cant directly calculate the datedif.

Let me try with a switch and see if it works based on max and edate