Create date bins from a date variable

[PaulGBG]

Hi,

I need to set up a report that uses a date variable from a slicer input to show customer outstanding amounts as at the date selected, and also grouped in dates 31 days from that date variable. 

I have the correct total $ amounts from a measure using the date variable.

Test Var 2 =

VAR RunDate22 = min('As at Date'[Due Date])

RETURN

CALCULATE(SUM('Detailed_Customer_ledger_entries'[Amount_LCY]),

(RunDate22 >= 'Detailed_Customer_ledger_entries'[Posting_Date])

)

I have been trying to use the same date variable in a calculated column to get the date bins, when I had a fixed date, I could do this using IF formulas that refer to the following calculated column (as below), the date variable didn't seem to work in a calculated column though to replace  DATE (2020,08,31).

Due Date Days = (1.* 'Cust_LedgerEntries'[Due_Date]- DATE(2020,08,31))*-1

I have been trying to put an IF statement in the measure just before the RETURN statement, but I couldn't get this to work.

So I would need one measure for all days that are between 1 and 31 days where the due date is older than the variable date for example.

What is the best way to do this please?

[amitchandak]

@PaulGBG ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[PaulGBG · Accepted solution]

Thanks for helping amitchandak 

Actually I just resolved this myself. The filters are within calculate, so all I needed to do was add in:

VAR RunDate22 = min('As at Date'[Due Date])

RETURN

CALCULATE(SUM('Detailed_Customer_ledger_entries'[Amount_LCY]),

(RunDate22 >= 'Detailed_Customer_ledger_entries'[Posting_Date]),

(RunDate22 - 'Cust_LedgerEntries'[Due_Date])>31,

(RunDate22 - 'Cust_LedgerEntries'[Due_Date])<63

)