Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi All,
I am stuck on the following...
I am trying to create the Closing Balance Inventory for 2020 e compare with the same period last year..
Solved! Go to Solution.
Hi @Anonymous
Does this return what you want?
Inventory Last Year =
CALCULATE(
[Inv. Sum],
SAMEPERIODLASTYEAR( 'Calendar'[Date] )
)
ALso, you need to mark your calendar table as a date table. That is when the magic happens. Right-click on it, select Mark as Date Table, then select the date column. Until you do that, it is just another table in the model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @Anonymous
Does this return what you want?
Inventory Last Year =
CALCULATE(
[Inv. Sum],
SAMEPERIODLASTYEAR( 'Calendar'[Date] )
)
ALso, you need to mark your calendar table as a date table. That is when the magic happens. Right-click on it, select Mark as Date Table, then select the date column. Until you do that, it is just another table in the model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingman.. after 4 hours on this **bleep** formula... I got it!!!! thanks for the help. really killed me!!! appreciate!
@Anonymous wrote:
man.. after 4 hours on this **bleep** formula... I got it!!!! thanks for the help. really killed me!!! appreciate!
What was your final measure?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingthanks a lot for the answer. I need to use the closingbalancequarter..
1. I marked the table as date
but it still giving me a error...
I'm not an expert by any means on the CLOSINGBALANCE* functions, but I believe those return the data at the end of the relevant quarter, which would be March 31, June 30, Sept 30, and Dec 31. That works on your Calendar table.
But those dates don't exist in your inventory table. Everything is the 1st of the month.
My measure above returns the following:
See this article near the bottom from SQLBI. It concludes the same thing I have. Emphasis mine.
"The STARTOFMONTH, STARTOFQUARTER, STARTOFYEAR, ENDOFMONTH, ENDOFQUARTER, and ENDOFYEAR functions are only useful when the dataset is complete with all the start and end dates of each time period. When this is not the case, you can implement a custom logic using the other techniques previously described in this article."
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@Anonymous do you have date dimension in your model? As a best practice, add date dimension in your model and use it for and time intelligence calculations. There are many posts on how to add date dimension and below is the link to a few. Once the date dimension is added, mark it as a date table on table tools.
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns
If not, add one, and use that in your calculations.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
hi, I have added the link to power BI, in case you want to have a look. thanks Simone
Thanks, I have tried already, but it doesn't work
User | Count |
---|---|
114 | |
107 | |
103 | |
57 | |
36 |
User | Count |
---|---|
149 | |
136 | |
101 | |
91 | |
62 |