Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
I have a dataset of accounts receivable items 01.01.2019 - 30.09.2019. The data includes things like document date - due date - clearing date. How can I build a table that shows what is open in a certain week based on the document date and the clearing date. I also want to see how much is cleared (=received) in that week. So there must be a split between open items and cleared items in that week. If something is cleared it shouldn't show anymore in the following week.
So i also want to add a chichlet slicer with all weeks of the year. I know how this works. Only not sure about what measures are required. I am decent with Power BI - but this is a next step for me. I am not familair with this.
I want to see this for a certain week:
Row 1. Received (cleared items in that week based on clearing date) -> 5K example
Row 2. Invoiced (new invoices created in that week based on document date) -> 10K example
Row 3. Open items from prior weeks (based on document & clearing date (or not cleared)) -> 50K example
If I need to add more information - please let me know - apologies in advance in that case.
Thanks,
Devon
Solved! Go to Solution.
Alright, I have refined the code a little, based on your values, so for the number of open documents it now looks like this:
Open documents = VAR _maxDate = MAX ( vDate[Date] ) VAR _minDate = MIN ( vDate[Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( 'Table'[Document Number] ); FILTER ( 'Table'; 'Table'[Clearing date] > _minDate && 'Table'[Document Date] < _maxDate ) ) + CALCULATE ( DISTINCTCOUNT ( 'Table'[Document Number] ); FILTER ( 'Table'; ISBLANK ( 'Table'[Clearing date] ) && 'Table'[Document Date] < _maxDate && _maxDate <= TODAY () ) )
A quick walkthrough of the code:
_minDate and _maxDate calculates the min and max date in the the current context. If you choose 1 week it will be the first and the last day of the week. If you choose a whole year, it will be the first and the last date of the year.
Next there is a part of code that starts with calculate. It counts the number of distinct document numbers which satisfy the following criteria:
Clearing date must be a later date than the first date in the current context.
Document date(*) must be an earlier date than the last date in the current context.
The table 'Table' will be filtered be these criterias, and the output will be number of distinct document numbers. With these criterias, when you select a spesific week, you will count all documents that were open at one point in that week. If was cleared on Wednesday, it counts as open that week. The nice thing about this definition is that if you select e.g. a whole year, you will see all documents that were open that year.
The second calculate-part handles documents which has no clearing date yet. This document will be counted according to these criterias:
Document date(*) must be an earlier date than the last date in the current context.
Last date in current context must be earlier or equal to todays date.
Hope this cleared things up a little for you. Here is the link to the power bi file: pbix
Hi @DevonVanDam ,
great description of your problem, the only ting missing is a relevant sample dataset.
One way to solve your problem is by creating a date dimension without connections to your dataset table and create measures like this:
Uncleared documents = CALCULATE ( DISTINCTCOUNT ( 'Table'[documentID] ); FILTER ( 'Table'; ISBLANK ( 'Table'[clearing date] ) && 'Table'[due date] >= MIN ( vDate[Date] ) && 'Table'[due date] <= MAX ( vDate[Date] ) ) )
and
Cleared documents = CALCULATE ( DISTINCTCOUNT ( 'Table'[documentID] ); FILTER ( 'Table'; 'Table'[clearing date] >= MIN ( vDate[Date] ) && 'Table'[clearing date] <= MAX ( vDate[Date] ) ) )
These measures don't do anything for me. How can I show open items per selected week / year.
I am using a year chiclet slicer and a week chichlet slicer. So I want to see open items per week. It is an open item based on the clearing date versus the dates in that week / year selected.
Can you help me further now you have seen my database @sturlaws ?
I don't really understand what the formules you mentioned are doing, how do I use them?
Alright, I have refined the code a little, based on your values, so for the number of open documents it now looks like this:
Open documents = VAR _maxDate = MAX ( vDate[Date] ) VAR _minDate = MIN ( vDate[Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( 'Table'[Document Number] ); FILTER ( 'Table'; 'Table'[Clearing date] > _minDate && 'Table'[Document Date] < _maxDate ) ) + CALCULATE ( DISTINCTCOUNT ( 'Table'[Document Number] ); FILTER ( 'Table'; ISBLANK ( 'Table'[Clearing date] ) && 'Table'[Document Date] < _maxDate && _maxDate <= TODAY () ) )
A quick walkthrough of the code:
_minDate and _maxDate calculates the min and max date in the the current context. If you choose 1 week it will be the first and the last day of the week. If you choose a whole year, it will be the first and the last date of the year.
Next there is a part of code that starts with calculate. It counts the number of distinct document numbers which satisfy the following criteria:
Clearing date must be a later date than the first date in the current context.
Document date(*) must be an earlier date than the last date in the current context.
The table 'Table' will be filtered be these criterias, and the output will be number of distinct document numbers. With these criterias, when you select a spesific week, you will count all documents that were open at one point in that week. If was cleared on Wednesday, it counts as open that week. The nice thing about this definition is that if you select e.g. a whole year, you will see all documents that were open that year.
The second calculate-part handles documents which has no clearing date yet. This document will be counted according to these criterias:
Document date(*) must be an earlier date than the last date in the current context.
Last date in current context must be earlier or equal to todays date.
Hope this cleared things up a little for you. Here is the link to the power bi file: pbix
Hi @sturlaws ,
Thank you in advance for your help and reply! I follow your measure, only question please:
what is vDate[Date]. Am I supposed to make some kind of date file (Excel)?
I have the below one, is that it? The 'Date' column?
Date | DayInWeek | DayOfMonth | DayOfWeekName |
1-1-2017 | 7 | 1 | Sunday |
2-1-2017 | 1 | 2 | Monday |
3-1-2017 | 2 | 3 | Tuesday |
4-1-2017 | 3 | 4 | Wednesday |
5-1-2017 | 4 | 5 | Thursday |
6-1-2017 | 5 | 6 | Friday |
7-1-2017 | 6 | 7 | Saturday |
8-1-2017 | 7 | 8 | Sunday |
9-1-2017 | 1 | 9 | Monday |
10-1-2017 | 2 | 10 | Tuesday |
When someone asks for help without attaching a demo file or sample dataset, I usually create dummy dataset and report to make sure the solution I provide actually works. vDate is just the date table of such a report. Replace it with your own date table in the code
Posting Date | Document Date) | Document Number | Vendor | Amount ƒ-LC | Clearing Document | Clearing date | Reference (Acc. Doc. Header) | Fiscal Year (Acc. Doc. Header) | Fiscal period (Acc. Doc. Header) |
26-6-2018 | 25-6-2018 | 1600120091 | 9963 | 284,16 | 1600120092 | 26-6-2018 | 6014347807 HV | 2018 | 6 |
26-6-2018 | 25-6-2018 | 1600120092 | 9963 | -284,16 | 1600120092 | 26-6-2018 | 6014347807 HV | 2018 | 6 |
8-11-2018 | 1-11-2018 | 1600120175 | 18384 | -97,15 | 1600120176 | 8-11-2018 | 90854197 HV | 2018 | 11 |
8-11-2018 | 1-11-2018 | 1600120176 | 18384 | 97,15 | 1600120176 | 8-11-2018 | 90854197 HV | 2018 | 11 |
8-1-2018 | 8-1-2018 | 2400224211 | 16686 | 123,23 | 2400224552 | 16-1-2018 | PR/11566 PV | 2018 | 1 |
8-1-2018 | 8-1-2018 | 2400224212 | 6996 | 777,49 | 2400224460 | 15-1-2018 | 90830452 PV | 2018 | 1 |
9-1-2018 | 9-1-2018 | 2400224277 | 150 | 62,12 | 2400225938 | 19-2-2018 | 90830653 HV | 2018 | 1 |
9-1-2018 | 20-12-2017 | 2400224306 | 10102 | -148,2 | 2400224306 | 9-1-2018 | 95197884 PV | 2018 | 1 |
10-1-2018 | 14-12-2017 | 2400224333 | 17198 | -82,42 | 2400224333 | 10-1-2018 | 80750C71Q PV | 2018 | 1 |
10-1-2018 | 10-1-2018 | 2400224334 | 10102 | 5,67 | 2400224683 | 18-1-2018 | 0028 PV | 2018 | 1 |
10-1-2018 | 9-1-2018 | 2400224343 | 10088 | 1425,85 | 2400231423 | 4-7-2018 | 180000639 HV+PV | 2018 | 1 |
11-1-2018 | 18-12-2017 | 2400224377 | 6996 | -2777,63 | 2400224377 | 11-1-2018 | 90826082 PV | 2018 | 1 |
11-1-2018 | 17-10-2017 | 2400224378 | 13955 | -89,15 | 2400224378 | 11-1-2018 | 607305 PV | 2018 | 1 |
11-1-2018 | 11-1-2018 | 2400224413 | 18719 | 452,5 | 2400224419 | 11-1-2018 | 95194465 PV | 2018 | 1 |
11-1-2018 | 3-1-2018 | 2400224414 | 1455 | 58,33 | 2400225602 | 8-2-2018 | 626934 PV | 2018 | 1 |
11-1-2018 | 11-1-2018 | 2400224415 | 18719 | 10,08 | 2400224420 | 11-1-2018 | 95196465 HV | 2018 | 1 |
11-1-2018 | 16-11-2017 | 2400224418 | 10088 | 2266,78 | 170029965 PV | 2018 | 1 | ||
11-1-2018 | 11-1-2018 | 2400224419 | 18719 | -452,5 | 2400224419 | 11-1-2018 | 95194465 PV | 2018 | 1 |
11-1-2018 | 11-1-2018 | 2400224420 | 18719 | -10,08 | 2400224420 | 11-1-2018 | 95196465 HV | 2018 | 1 |
12-1-2018 | 24-8-2017 | 2400224438 | 150 | -836,18 | 2400224438 | 12-1-2018 | 90820043 HV+PV | 2018 | 1 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |