Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am building a dashboard and want to visually display my data to show:
1. The total number of requests received for the month,
2. The change (increase or decrease in %) in the total requests received from last month's total,
3. How many received in the last month are still open - this will be the opening balance for the current month.
Request ID | Received Date | Actual Completion Date | Received Month | Status |
101 | 1/08/2020 | 28/08/2020 | Aug | Completed |
102 | 2/08/2020 | 11/08/2020 | Aug | Completed |
103 | 05/08/2020 | Aug | In Progress | |
104 | 12/08/2020 | 23/08/2020 | Aug | Completed |
105 | 19/08/2020 | Aug | In progress | |
106 | 22/08/2020 | 02/09/2020 | Aug | Completed |
107 | 03/09/2020 | 14/09/2020 | Sept | Completed |
108 | 05/09/2020 | Sept | In progress | |
109 | 10/09/2020 | 16/09/2020 | Sept | Completed |
Say, if I am creating the report as of Sept end, then I require the following details:
1. Total Requests for Aug = 6
2. Total Requests for Sept = 3
3. Variance/decrease in requests = 3
4. Opening balance for Sept = 3 (including request 106 which was in progress on 31st Aug).
I did try using the time intelligence functions, but it is not working.
The measure created to calculate the previous month total
MdxScript(Model) (6, 72) Calculation error in measure 'TestWorkFlow'[Prev MTD Req]: A column specified in the call to function 'PREVIOUSMONTH' is not of type DATE. This is not supported.
How can I display the above 4 details I require?
Solved! Go to Solution.
Hi @Anonymous ,
You may create a calendar table first of all, then create relationship with your fact table TestWorkFlow[Received Date] on the date field .
Calendar= CALEANDARAUTO()
Then you may create measures like DAX below.
Curr MTD Req= CALCULATE(COUNTROWS(TestWorkFlow),DATESMTD(Calendar[Date]))
Prev MTD Req= CALCULATE(COUNTROWS(TestWorkFlow),DATESMTD(ENDOFMONTH(DATEADD(Calendar[Date],-12,MONTH))))
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
You may create a calendar table first of all, then create relationship with your fact table TestWorkFlow[Received Date] on the date field .
Calendar= CALEANDARAUTO()
Then you may create measures like DAX below.
Curr MTD Req= CALCULATE(COUNTROWS(TestWorkFlow),DATESMTD(Calendar[Date]))
Prev MTD Req= CALCULATE(COUNTROWS(TestWorkFlow),DATESMTD(ENDOFMONTH(DATEADD(Calendar[Date],-12,MONTH))))
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous, do you have a date table in your data model? If not, it's highly recommended to add one. This will facilitate time intelligence calculations.
The function PREVIOUSMONTH requires a date column (your formula uses the month).
Proud to be a Super User!
I am quite new to Power BI and trying to figure my way. I do not have a date table in my data model.
Can I get the desired results (last month balance etc.) without using time intelligence functions?
ActualCompletionDate is a date column and what I am trying to do is: count the number of records where the 'completion date' is from the previous month.
@Anonymous, here's a link that explains how to create a date table. A date table is extremely useful, and without it you will have difficulty doing certain calculations.
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Once you create a date table and join it to your data table, this is how your measure will look:
Prev MTD Req =
CALCULATE ( COUNTROWS ( TestWorkFlow ), PREVIOUSMONTH ( 'Date'[Date] ) )
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |