Start of month total based on app status (with statuses carrying over from previous months)

[Anonymous]

I am looking to created a start of month measure when an app is in a certain status (carried over from previous months).   I'd like the measure to calculate the start of month total for any Application currently in Approved, Docs Sent, and Docs Received status.  For instance with the below data:

Application Number	Status Date	Status	Amount
1	7/15/2020	Approved	$15,000.00
1	7/13/2020	Docs Sent	$15,000.00
1	9/1/2020	Funded	$15,000.00
2	7/16/2020	Approved	$10,000.00
3	8/2/2020	Docs Sent	$20,000.00
3	8/15/2020	Docs received	$20,000.00
3	8/15/2020	Funded	$20,000.00
4	8/15/2020	Approved	$9,000.00
4	9/15/2020	Funded	$9,000.00
5	9/2/2020	Approved	$18,000.00

I'd expect a start of month measure for Augst to return a total of $25,000, September total of $34,000 and an October total of $28,000.

[amitchandak]

@Anonymous , your number is not matching with data shared so difficult to understand. Can you explain with example?

With Time intelligence you can use the following for this month vs last month

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))

[Anonymous]

@amitchandak 

With the below data:

Application Number	Status Date	Status	Amount
1	7/15/2020	Approved	$15,000.00
1	7/16/2020	Docs Sent	$15,000.00
1	9/1/2020	Funded	$15,000.00
2	7/16/2020	Approved	$10,000.00
3	8/2/2020	Docs Sent	$20,000.00
3	8/15/2020	Docs received	$20,000.00
3	8/15/2020	Funded	$20,000.00
4	8/15/2020	Approved	$9,000.00
4	9/15/2020	Funded	$9,000.00
5	9/2/2020	Approved	$18,000.00

On August 1st I'd expect a start of month measure to return a total amount of $25,000 (App Amount for #1 & #2), on September 1st I'd expect a total of $34,000 (App Amount for #1, #2 & #4) and on October 1st total of $28,000 (App Amount for #2 & #5).  The measure would be looking to see the last status the App is in, and if its Approved, Docs Sent, or Docs Received add the Amount of the apps together.  I guess it could loos to see what status the App was in on the last day of the previous month, but if an App's Status hasn't changed from one month to the next, it needs to be included in the new end of month or start of month measure.

The Status Heirarchy is Approved< Docs Sent< Docs Received< Funded.

[v-easonf-msft · Accepted solution]

Hi， @Anonymous 

Please try steps as below:

1.create a calendar table first
such as :

calendar = CALENDAR(DATE(2020,1,1),DATE(2021,1,1))

2. Create a measure "Amount2" as below:

Amount2 = sumx(SUMMARIZE('Table','Table'[Application Number],'Table'[Amount],"number",COUNTROWS('Table')),'Table'[Amount])

3.Then you need to create a visual control measure to filter the data:
1)create a calculated column first:

Status flag = SWITCH('Table'[Status],"Approved",1,"Funded",-1,0)

2)Then create  measure [status flag2] ,and apply it to visual filter pane:

Status flag2 = CALCULATE(SUM('Table'[Status flag]),FILTER('Table','Table'[Status Date]<SELECTEDVALUE('Table 2'[Date])))

 The result will show as below:

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.