Forum Discussion
DAX Meassure
Dear Community members,
I have an Excel spreadsheet like the example (Table 1) below and I connected it to Power BI.
Table 1
| Author | Opening Date | Closing Date |
| John | 01-11-21 | 05-03-22 |
| Jane | 01-11-21 | 10-03-22 |
| John | 03-12-21 | 16-01-22 |
| Jane | 19-12-21 | 03-01-22 |
| John | 21-12-21 | 08-02-22 |
| Jane | 03-01-22 | 09-01-22 |
| John | 12-01-22 | 26-02-22 |
| Jane | 16-01-22 | 29-01-22 |
| John | 03-02-22 | 08-02-22 |
| Jane | 05-02-22 | 13-02-22 |
| John | 18-02-22 | 05-03-22 |
| Jane | 04-03-22 | |
| John | 08-03-22 | |
| Jane | 01-04-22 | |
| John | 08-04-22 |
I need to make the mentioned measurements. (Power BI visuals 2)
1. Requests from a previous period (Opening Balance)
2. New requests opened in the current period (New assigned)
3. Requests closed in the current period (Closed)
4. Outstanding Requests (Outstanding)
Power BI visuals
| Month | Opening Balance | New assigned | Closed | Outstanding |
| Nov-21 | 0 | 2 | 0 | 2 |
| Dec-21 | 2 | 3 | 0 | 5 |
| Jan-22 | 5 | 3 | 3 | 5 |
| Feb-22 | 5 | 3 | 4 | 4 |
| Mar-22 | 4 | 2 | 3 | 3 |
| Apr-22 | 3 | 2 | 0 | 5 |
| May-22 | 5 |
Can anyone help?
Thanks in advance
Nizami
Hi Anonymous ,
Maybe my understanding is wrong, please correct me if I'm wrong.
Here's my result,
I do this by first creating a calculated table, crossjoin the calendar table and the main table, and then counting. You could download the attachment for details.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Anonymous , Refer to the HR blog
Opening =
CALCULATE(COUNTx(FILTER('Table','Table'[Opening Date]<=Min('Date'[Date]) && (ISBLANK('Table'[Closing Date]) || 'Table'[Closing Date]>min('Date'[Date]))),('Table'['Table' Id ])),CROSSFILTER('Table'[Opening Date],'Date'[Date],None))
Closing =
CALCULATE(COUNTx(FILTER('Table','Table'[Opening Date]<=max('Date'[Date]) && (ISBLANK('Table'[Closing Date]) || 'Table'[Closing Date]>max('Date'[Date]))),('Table'['Table' Id ])),CROSSFILTER('Table'[Opening Date],'Date'[Date],None))
if you keep all join inactive you can remove
CROSSFILTER('Table'[Opening Date],'Date'[Date],None)
Hi Anonymous ,
Maybe my understanding is wrong, please correct me if I'm wrong.
Here's my result,
I do this by first creating a calculated table, crossjoin the calendar table and the main table, and then counting. You could download the attachment for details.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
