Calculate opening balance and last month and YTD total

[Anonymous]

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 

Prev MTD Req = CALCULATE(COUNTROWS(TestWorkFlow), PREVIOUSMONTH('TestWorkFlow'[ActualCompletionDate].[Month]))

However, I got the following error message : 

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?

[v-xicai · Accepted 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.

[DataInsights]

@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).

[Anonymous]

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. 

[DataInsights]

@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] ) )