Calculate the revenue only for the last day of the quarter

[ChPetru]

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.

[Anonymous]

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

[amitchandak]

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

[ChPetru]

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:

QEndDate = calculate(LASTDATE(DateMaster[Master Date]),VALUES(DateMaster[FiscalYearQuarter])), but instead of populating the column with just the last day of each quarter, I simply get the DateMaster[Master Date] values

[ChPetru · Accepted 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!

QEndDatte = calculate(LASTDATE(DateMaster[Master Date]),VALUES(DateMaster[FiscalYearQuarter]))