Hello guys,
I am working on a for me very complex Power BI report, I have done some 'basic' Sales analysis etc, but the Dynamics are a level up for me. I'm working in Accounts Receivable and my manager wants to switch between years and weeks to see what is:
- Newly invoiced (invoice sent to customer)
- Still open
- Paid (payment received)
For new documents I have the attached DAX which seems to be working perfect:
New documents =
CALCULATE (
DISTINCTCOUNT( Data[Document Number] ),
FILTER (
Data,
Data[Posting Date] >= MIN (DateTable[Date])
&& Data[Posting Date] <= MAX (DateTable[Date])))
After this I take this measure in to the visual filter and filter on greater then 0, because it counts the documents. Any upgrades on this I am also open to that.
Now I also need one that shows me which items are still open, I have something which is partially working, but it is only showing me what is open from the items posted in the week (and year) I have selected. Does anyone know how I can upgrade the below formula:
The variables I have are:
Document number, document date, clearing document, clearing date. If there is a clearing date, it is means it is paid, but if I selected an earlier week then the clearing date, it should show the invoice as open. So it should show as open when the clearing date before the current week's dates or when the clearing date field is empty.
Uncleared documents =
VAR _maxDate =
MAX ( DateTable[Date] )
VAR _minDate =
MIN ( DateTable[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( Data[Document Number] ),
FILTER (
Data,
Data[Clearing date] > _minDate
&& Data[Posting Date] < _maxDate
)
)
+ CALCULATE (
DISTINCTCOUNT ( Data[Document Number] ),
FILTER (
Data,
ISBLANK ( Data[Clearing date] )
&& Data[Posting Date] < _maxDate
&& _maxDate <= TODAY ()
)
)
So in here I would also put this measure in the slicers and make the filter on is greater then 0.
Does anyone know how I can have it to show everything that is still open from prior weeks aswell?
Thanks,
Devon
