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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

Urgent: Help with creating a better date function, that round up and down to full months.

Hey community. Please help fix my date formula below.

 

I have a question for the formula below that i have used to see financial data comparisons going back to "previous period", which basically means that the selected date range going back in time by the same selected date range.

 

Revenue last period = 
VAR PeriodEnd = FIRSTDATE(Dates[Date])-1
VAR PeriodStart =
    PeriodEnd -
    DATEDIFF (
        FIRSTDATE(Dates[Date]),
        LASTDATE(Dates[Date]),
        DAY
    )
VAR Result = 
    CALCULATE(
        [Revenue],
        DATESBETWEEN(
            Dates[Date],
            PeriodStart,PeriodEnd
        )
)        
RETURN
    Result

 

 

The issue is that on some type of dates it works, and on others it doesn't, (depends on how many days in between)

 

If i select third quarter (european date setting) - (01/07/2022) - (30/09/2022) then it outputs a number i don't understand.

If i however remove the (-1) from the

 

FIRSTDATE(Dates[Date])-1

 

 

Then it outputs the "semi" correct number from 01/04/2022 - 01/07/2022 - however it should use end date 30/06/2022 instead of 01/07/2022. 

 

That's the first part of the problem.

The second part is, if i select dates from (01/01/2022) - (30/06/2022), it should use the date range (01/07/2021 - 31/12/2021).

The date difference is 180 days, which results in = (05/07/2021) - (01/01/2022) - HOWEVER that "semi" works if i input the (-1) again

 

FIRSTDATE(Dates[Date])-1

 

 

Then it selects (05/07/2021) - (31/12/2021), again it should round to nearest month in this case to (01/07/2021) - (31/12/2021)

 

To my knowledge i cannot use the Datesinperiod function, because the specified dates in Power bi can be selected manually, when they need quarter report they just put in the quarter dates they want, and when they want to see half year, they just put in half year.

 

Can there be done anything to the function that makes it work all around, no matter what date is selected? It basically just needs to round to nearest month, and take into consideration of the "end month".

 

Thanks a lot in advance.

 

3 REPLIES 3
Anonymous
Not applicable

Can anyone assist please. We are willing to pay if necessary.

amitchandak
Super User
Super User

@Anonymous , refer if this logic can help

 

Same Date range last period =
var _diff = datediff(MIN('Date'[date]),max('Date'[date]),DAY)
var _p_st_date = MINX('Date',DATEADD('Date'[date],-1*_diff,Day))-1
var _p_end_date = MAXX('Date',DATEADD('Date'[date],-1*_diff,Day))-1
Return
CALCULATE(sum(Sales[Sales Amount]),all('Date'[date]),'Date'[date]>=_p_st_date && 'Date'[date]<= _p_end_date
)

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
Anonymous
Not applicable

Thank you for helping @amitchandak . But the formula retrives the same result.

 

With previous formula

tobkres_0-1670319719234.png

With your formula

tobkres_1-1670319734775.png

The correct value that should be displayed is 01-04-2022 - 30-06-2022

tobkres_2-1670319819383.png

 

Somehow we need the formula to count number of days between the dates in full months selected.

I found out i could make a datediff in months, but that would just return the number 3, and if i use that in the formula, it would only go 3 days back.

If that makes sense?

 

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.