cancel
Showing results for
Did you mean:
New Member

## Create date bins from a date variable

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?

1 ACCEPTED SOLUTION
New Member

Thanks for helping

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
)
2 REPLIES 2
Super User IV

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

Proud to be a Super User!

New Member

Thanks for helping

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
)

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!