cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

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
)

View solution in original post

2 REPLIES 2
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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
)

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors