Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate opening balance and last month and YTD total

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 IDReceived DateActual Completion DateReceived MonthStatus
1011/08/202028/08/2020AugCompleted
1022/08/202011/08/2020AugCompleted
10305/08/2020 AugIn Progress
10412/08/202023/08/2020AugCompleted
10519/08/2020 AugIn progress
10622/08/202002/09/2020AugCompleted
10703/09/202014/09/2020SeptCompleted
10805/09/2020 SeptIn progress
10910/09/202016/09/2020SeptCompleted

 

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?

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

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.

View solution in original post

4 REPLIES 4
v-xicai
Community Support
Community Support

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
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.