Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Solved! Go to Solution.
Hi @YZORI ,
My solution don't need the calendar table.
Create a Year column and a Month column in your main table.
Create a measure to return the latest date in the same month of last year.
The latest date in the same month of last year =
CALCULATE (
MAX ( 'PowerQuery_Inventory'[FileCreationDate] ),
FILTER (
ALLSELECTED ( PowerQuery_Inventory ),
[Year]
= MAX ( 'PowerQuery_Inventory'[Year] ) - 1
&& [Month] = MAX ( 'PowerQuery_Inventory'[Month] )
)
)
You can create another metric or, like me, modify the metric to get the sum of the values of the latest date of the same month last year.
Inventory Last Year =
VAR latestdate =
CALCULATE (
MAX ( 'PowerQuery_Inventory'[FileCreationDate] ),
FILTER (
ALLSELECTED ( PowerQuery_Inventory ),
[Year]
= MAX ( 'PowerQuery_Inventory'[Year] ) - 1
&& [Month] = MAX ( 'PowerQuery_Inventory'[Month] )
)
)
RETURN
CALCULATE (
SUM ( PowerQuery_Inventory[RetailAmt] ),
FILTER ( ALLSELECTED ( PowerQuery_Inventory ), [FileCreationDate] = latestdate )
)
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @YZORI ,
My solution don't need the calendar table.
Create a Year column and a Month column in your main table.
Create a measure to return the latest date in the same month of last year.
The latest date in the same month of last year =
CALCULATE (
MAX ( 'PowerQuery_Inventory'[FileCreationDate] ),
FILTER (
ALLSELECTED ( PowerQuery_Inventory ),
[Year]
= MAX ( 'PowerQuery_Inventory'[Year] ) - 1
&& [Month] = MAX ( 'PowerQuery_Inventory'[Month] )
)
)
You can create another metric or, like me, modify the metric to get the sum of the values of the latest date of the same month last year.
Inventory Last Year =
VAR latestdate =
CALCULATE (
MAX ( 'PowerQuery_Inventory'[FileCreationDate] ),
FILTER (
ALLSELECTED ( PowerQuery_Inventory ),
[Year]
= MAX ( 'PowerQuery_Inventory'[Year] ) - 1
&& [Month] = MAX ( 'PowerQuery_Inventory'[Month] )
)
)
RETURN
CALCULATE (
SUM ( PowerQuery_Inventory[RetailAmt] ),
FILTER ( ALLSELECTED ( PowerQuery_Inventory ), [FileCreationDate] = latestdate )
)
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you @v-stephen-msft - I have modified it slightly to make it work for my working file - but this put me in the right direction.
INV PY ($) =
CALCULATE(
SUM(PowerQuery_inventory[RetailAmt]),
FILTER(
ALL(PowerQuery_inventory),
YEAR(PowerQuery_inventory[FileCreationDate]) = YEAR(TODAY())-1 &&
MONTH(PowerQuery_inventory[FileCreationDate]) = MONTH(DATEADD(DATESYTD(TODAY()), -12, MONTH)) &&
PowerQuery_inventory[FileCreationDate] = CALCULATE(
MAX(PowerQuery_inventory[FileCreationDate]),
ALL(PowerQuery_inventory),
YEAR(PowerQuery_inventory[FileCreationDate]) = YEAR(TODAY())-1,
MONTH(PowerQuery_inventory[FileCreationDate]) = MONTH(DATEADD(DATESYTD(TODAY()), -12, MONTH))
)
)
)
//////You can copy and paste this code into your DAX editor and replace "PowerQuery_inventory" with the name of your inventory table.
Hey @bhelou unfortanutely this didn't work for me. I've added a sample of the data here for your reference: sample_data.pbix
Hello , please can you share some sample of the PBIX file ? , its a bit hard to guess what is happening here for the last year calculation .
you can share a link via dropbox or drive .
Regards ,
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |