• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Balance of previous day and previous last day of month

Gman

Member
Hello -
I have a table that contains a receivables account. Obviously with that the receivables is a rolling account. I have some situations that I need to us previous day and previous month ending receivables. I need to make a pivot that shows the beginning balance (end of previous day) of rec + sales - payments = ending receivables. I also have some calcs that use the ending receivables balance of the previous month. I was thinking I would make a new column for each with a DAX formula but it doesn't seem to be working. Here is what I was attempting for the end of previous month Receivables balance.
=CALCULATE([Sum of Balance],DATESBETWEEN(CD128TABLE[RPT_DATE],EOMONTH([Today],-1),EOMONTH([Today],-1))) but it doesn't show anything. The reason it is Sum of balance is because there are different divisions. I've also tried
=CALCULATE([Sum of Balance],FILTER(Dim_Date[Date],EOMONTH([Today],-1))). Any ideas?
 
Try...
=CALCULATE([Sum of Balance], FILTER(CD128TABLE, CD128TABLE[RPT_DATE]=EOMONTH([Today],-1)))

If above does not work. Upload sample workbook with all tables and relationships (with sanitized data).
 
Thank you Chihiro worked perfectly. Any ideas how to get previous day? I tried
BalanceEndOfPreviousDay:=CALCULATE([Sum of Balance],FILTER(CD128TABLE,CD128TABLE[RPT_DATE]-1)) But that doesn't seem to work.
 
Thank you Chihiro worked perfectly. Any ideas how to get previous day? I tried
BalanceEndOfPreviousDay:=CALCULATE([Sum of Balance],FILTER(CD128TABLE,CD128TABLE[RPT_DATE]-1)) But that doesn't seem to work.
also tried
BalanceEndOfPreviousDay:=CALCULATE([Sum of Balance],FILTER(CD128TABLE,CD128TABLE[RPT_DATE]=PREVIOUSDAY(CD128TABLE[RPT_DATE])))
 
Try...
=CALCULATE([Sum of Balance], FILTER(CD128TABLE, CD128TABLE[RPT_DATE]=[Today]-1))

Or
=CALCULATE([Sum of Balance], FILTER(CD128TABLE, CD128TABLE[RPT_DATE]=DATE(YEAR([Today]),MONTH([Today]),DAY([Today])-1)))
 
Back
Top