Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello, I am looking for a way to to calculate the revenue only in the last day of the quarter.
Let's say I am looking (filtering) on Q3FY21, I want the "CustomRevenue" metric to show me the revenue associated to the last day of the fiscal quarter of the particular period.
I have already mapped the fiscal quarter in a separate column, so I need the revenue of the last day of that particular fiscal quarter.
Solved! Go to Solution.
Actually my formula works! I just have to use it on a measure, not calculated column, since at row level it does not apply on the whole dataset!
You can use similar logic like this :
last day of quater =
VAR LastSaleDate =
CALCULATE (
MAX ( 'calender table'[Date] ),
FILTER ( calender table, CALCULATE ( SUM ( 'sales table'[sales] ) ) > 0 )
)
RETURN
CALCULATE (sum(
'sales table'[sales],
FILTER (
tbl_Calendar_ph,
YEAR ( 'calender table'[Date] ) = YEAR ( LastSaleDate )
&& QUATER( LastSaleDate ) = 'calender table'[quater Number]
)
)
@ChPetru , You need a measure like this.
QTD Sales = CALCULATE(lastnonblankvalue('Date'[Date],SUM(Sales[Sales Amount])),DATESQTD(('Date'[Date])))
for a selected range, you can avoid datesqtd
QTD Sales = CALCULATE(lastnonblankvalue('Date'[Date],SUM(Sales[Sales Amount])))
Thank you amitchandak, however DATESQTD uses calendar year quarters. I have a custom fiscal year mapping with first day of the fiscal year starting in 1st of February. I have already calculated the Fiscal Quarter column, just need to find a way to flag the last 'Date'[Date] of the custom fiscal quarters as "True", while every other day that is not an end of quarter to be "False".
Or, i have tried this:
Actually my formula works! I just have to use it on a measure, not calculated column, since at row level it does not apply on the whole dataset!
User | Count |
---|---|
100 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |