Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dears,
I've been struggling for the past few days to come up with a formula which calculates SAMEPERIODLASTYEAR Visits.
So far i've come up with:
YTD = CALCULATE(SUM(Main[Visits]),DATESYTD(DateKey[Date].[Date]))
YTD LY = CALCULATE([YTD],SAMEPERIODLASTYEAR(DateKey[Date].[Date]))
YTD - Work just fine!
The problem is YTD LY, it returns the sales for the whole year.
The values shouldnt be so far apart.
I'm also mentioning that i have a DateKey Table with tabes linked to my facts table through Date (1:1 relationship).
Getting this solved would be a huge deal for me.
Any tips?
BR,
Andrei
Solved! Go to Solution.
@Anonymous thanks!
That i have figured it out myself. The problem is that i need to have these values without any date filter in the table.
If i simply put YTD and YTD LY in one table it doesn't work.
This printscreen is from you pbix file.
I did one thing though and it worked.
YTD =
IF (
MIN ( Main[Datee] ) <= CALCULATE ( MAX ( Main[Datee] ), ALL ( Main ) ),
CALCULATE ( SUM ( Main[Visits] ), DATESYTD ( Main[Datee] ) )
YTD LY (limited by last date in Data table) =
VAR DataMaxDate =
CALCULATE ( MAX ( Main[Datee] ), ALL ( Main ) )
RETURN
CALCULATE (
[YTD 2],
SAMEPERIODLASTYEAR (
INTERSECT (
VALUES ( DateKey[Date].[Date] ),
DATESBETWEEN ( DateKey[Date].[Date], BLANK (), DataMaxDate )
)
)
)
I've limited the date in my Main table to the last date i have values in it.
That's the only thing i found that works without using Date as filter (as you suggested)
BR,
Andrei
What happens when you add Dates column to your report table?
What values does YTD LY show now?
Please send a printscreen
@Anonymous seems like YTD LY calculates visits for the whole year. It shoud only calculate for the same period of this year according to the formula..
YTD LY = CALCULATE([YTD],SAMEPERIODLASTYEAR(DateKey[Date].[Date]))
YTD = CALCULATE(SUM(Main[Visits]),DATESYTD(DateKey[Date].[Date]))
BR,
Andrei
Hi @avelio
I have recreated your formulae and they work just fine:
YTD LY for 201701 is 296 as is YTD for 201601
Please see the file attached:
https://1drv.ms/u/s!AoP_9ampPIT7jQIzMzSXbamGsno0
@Anonymous thanks!
That i have figured it out myself. The problem is that i need to have these values without any date filter in the table.
If i simply put YTD and YTD LY in one table it doesn't work.
This printscreen is from you pbix file.
I did one thing though and it worked.
YTD =
IF (
MIN ( Main[Datee] ) <= CALCULATE ( MAX ( Main[Datee] ), ALL ( Main ) ),
CALCULATE ( SUM ( Main[Visits] ), DATESYTD ( Main[Datee] ) )
YTD LY (limited by last date in Data table) =
VAR DataMaxDate =
CALCULATE ( MAX ( Main[Datee] ), ALL ( Main ) )
RETURN
CALCULATE (
[YTD 2],
SAMEPERIODLASTYEAR (
INTERSECT (
VALUES ( DateKey[Date].[Date] ),
DATESBETWEEN ( DateKey[Date].[Date], BLANK (), DataMaxDate )
)
)
)
I've limited the date in my Main table to the last date i have values in it.
That's the only thing i found that works without using Date as filter (as you suggested)
BR,
Andrei
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |