March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
Hey everyone,
I've been trying to create a measure that returns sales figures for the same period last year but have only been able to get the DAX statement to return the sales figures for the entire year, not the sales figures YTD Last Year. I've copied in two DAX functions I've tried, both returning the entire year value instead of the YTD period last year. Utlimately, I want to display this measure on a card on my dashboard. Hope this makes sense.
The DAX for these measures was sourced from various community formus here, so I've been doing some digging but haven't had success.
Tires YTD LY =
CALCULATE([2021+22 Tire Sales], SAMEPERIODLASTYEAR('VI_PS_TKT_HIST_LIN (2021+22 TIRES)'[BUS_DAT]), FILTER ('VI_PS_TKT_HIST_LIN (2021+22 TIRES)', 'VI_PS_TKT_HIST_LIN (2021+22 TIRES)'[BUS_DAT] < TODAY()))
///
TiresYTD LY =
VAR DataMaxDate =
CALCULATE ( MAX ('VI_PS_TKT_HIST_LIN (2021+22 TIRES)'[BUS_DAT]), ALL ( Data ) )
RETURN
CALCULATE (
[YTD],
SAMEPERIODLASTYEAR (
INTERSECT (
VALUES ( 'VI_PS_TKT_HIST_LIN (2021+22 TIRES)'[BUS_DAT] ),
DATESBETWEEN ( 'VI_PS_TKT_HIST_LIN (2021+22 TIRES)'[BUS_DAT], BLANK (), DataMaxDate )
)
)
)
@Greg_Deckler Thanks for the quick reply!
I should have mentioned i'm working with direct query SQL databases and not tables that i've generated myself.
I'm not overly advanced with DAX but know enough to modify provided code. Would you be able to point me in the direction to get these DAX functions to work with SQL databases? I'm trying to sum sale quantities YTD last year and the SQL databases have a date tied to each sale quantity. Anything i've been able make work just returns the entire year sales quantity.
Your Time Intelligence TotalLYTD is what i'm looking to create with a measure displayed on a card.
@samwr You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
108 | |
75 | |
53 | |
52 | |
44 |
User | Count |
---|---|
160 | |
112 | |
69 | |
61 | |
50 |