Forum Discussion

PaulGBG's avatar
PaulGBG
Frequent Visitor
5 years ago
Solved

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?
 
Need Help
  • PaulGBG's avatar
    PaulGBG
    5 years ago

    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

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

    • PaulGBG's avatar
      PaulGBG
      Frequent Visitor
      5 years ago

      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
      )