Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
@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]))
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |