cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II

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

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".

3 REPLIES 3
Helper II

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

Super User

@tobkres , refer if this logic can help

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

Helper II

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

With previous formula

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

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?

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors