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 need help creating a QTD measure that will allow me to sum values differently based on the value description. I have Table A with contains various values that are daily sums or End of Month snapshots. The following Measure works only for daily sums but not End of Month Snapshots:
CALCULATE (
[Amount], // Sum(TableA[Value])
REMOVEFILTERS ( 'Date' ),
'Date'[Year Month Number] <= LastMonthAvailable,
'Date'[Year Quarter Number] = LastYearQuarterAvailable
)
Date | Description | Value | Desired Outcome if 8/31 is selected | |
7/1/2020 | Income | 5 | Income is aggregated by Day | |
7/2/2020 | Income | 6 | ||
7/3/2020 | Income | 7 | ||
…. | ||||
8/31/2020 | Income | 5 | ||
1/31/2020 | # Accounts | 100 | # Accounts contains latest month snapshot | |
2/28/2020 | # Accounts | 120 | ||
3/31/2020 | # Accounts | 130 | ||
… | ||||
8/31/2020 | # Accounts | 150 |
Solved! Go to Solution.
@ae19bu , You have closingbalanceQuarter, that can date the last day balance of the closingbalanceQuarter. Also, you have the last nonblankvalue
closingbalanceQuarter(Sum('Table'[Value]), Date[Date])
Please date calendar with that QTD Total
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last Day QTD total
Now with lastnonblankvalue, I using table date not the date from date table to make sure if balance is not there on last date it take last avaiable date
QTD Sales = CALCULATE(lastnonblankvalue(Sales[Date],SUM(Sales[Sales Amount])),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(lastnonblankvalue(Sales[Date],SUM(Sales[Sales Amount])),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
I have video for this - https://youtu.be/yPQ9UV37LOU
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
@ae19bu , You have closingbalanceQuarter, that can date the last day balance of the closingbalanceQuarter. Also, you have the last nonblankvalue
closingbalanceQuarter(Sum('Table'[Value]), Date[Date])
Please date calendar with that QTD Total
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last Day QTD total
Now with lastnonblankvalue, I using table date not the date from date table to make sure if balance is not there on last date it take last avaiable date
QTD Sales = CALCULATE(lastnonblankvalue(Sales[Date],SUM(Sales[Sales Amount])),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(lastnonblankvalue(Sales[Date],SUM(Sales[Sales Amount])),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
I have video for this - https://youtu.be/yPQ9UV37LOU
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
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 |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
68 | |
48 | |
45 | |
18 | |
15 |