Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ChPetru
Helper I
Helper I

Calculate the revenue only for the last day of the quarter

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.

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.