Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi All,
I got requirement to calculate latest inventory below condition .
My matrix report structure below :
Year 2018 2019 2020
Inventory 50. 60. 70
Above actually drill down report . Year & month level .
1 . I want show last day of year value
Ex . In 2018 below it should show only 31 dec 2018 inventory value .
2. again if they drill down to month level I want to see each month last day value
Ex , report like below
Year 2018
Month 31jan 28 Feb 31 March. .. etc
Inventory 10 10 5 etc ..
They want to latest value
Solved! Go to Solution.
Last day inventory is the latest stock part, dateadd minus 1 year to see the value of the year before?
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Your requirements are not totally clear... but you might try this:
[Your Transformed Measure] =
SWITCH( TRUE(),
ISINSCOPE( Date[Month] ),
CALCULATE(
[Your Base Measure],
LASTDATE( Date[Date] )
),
ISINSCOPE( Date[Year] ),
CALCULATE(
[Your Base Measure],
SAMEPERIODLASTYEAR(
LASTDATE( Date[Date] )
)
)
)
Best
D
Hi @Anonymous @stevedep ,
In short my requirements are :
I am looking for to find out the latest inventory value of the year .
For ex ,
1.2018 , measure should show the value on 31dec 2018
2. 2019 , measure should show the 31dec 2019 value
3. 2020 should show the value captured till last complete month 31 may 2020 only , not the cumlative jan 1 2020 to june 8th 2020 .
Hi,
Its working on month level, but I am not able (yet) to get it working on year level:
LastestStock =
var __DateInContext = MAX(DateDimv2[Date].[Date])
var __PreviousMonth = CALCULATE(MAX(DateDimv2[Date]);ALL(DateDimv2[Date]);PREVIOUSMONTH(DateDimv2[Date]))
var __LastKnownDate =
CALCULATE (
LASTNONBLANK (
DateDimv2[Date];
CALCULATE(SUM('Fact-DailyStock'[Stock]))
);
DateDimv2[Date] < __DateInContext
)
var __LastKnownDatePreviousMonth =
CALCULATE (
LASTNONBLANK (
DateDimv2[Date];
CALCULATE(SUM('Fact-DailyStock'[Stock]))
);
DateDimv2[Date] < __PreviousMonth
)
return
if(EOMONTH(TODAY();0)<EOMONTH(__DateInContext;0);BLANK();
if(
EOMONTH(TODAY();0) > EOMONTH(__DateInContext;0);
CALCULATE(CALCULATE(SUM('Fact-DailyStock'[Stock])); FILTER(ALL(DateDimv2[Date]);DateDimv2[Date]=__LastKnownDate));
CALCULATE(CALCULATE(SUM('Fact-DailyStock'[Stock])); FILTER(ALL(DateDimv2[Date]);DateDimv2[Date]=__LastKnownDatePreviousMonth)))
)
Link to Power BI file here.
Hope it helps though (who cares about year level anyways ;-).
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
For the latest stock you can look at:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Stuck-in-DAX-Create-calculated-table-from-dat...
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi @stevedep ,
I am looking for to find out the year last day inventory available value .
Ex, 2020 should show the value captured on 31 may 2020 only , not the cumlative jan 1 2020 to may 31 2020 .
Last day inventory is the latest stock part, dateadd minus 1 year to see the value of the year before?
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
34 | |
14 | |
14 | |
12 | |
9 |