cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
simo12447
Helper III
Helper III

Closing Balance Inventory Prior Year

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..

 

Inventory SUM = (CALCULATE(SUM(Final[Value]),Final[Type] = "Inventory"))
Inventory 2020 = CLOSINGBALANCEQUARTER([Inventory SUM],Final[Date])
Inventory 19 = CALCULATE(CLOSINGBALANCEQUARTER([Inventory SUM],DATEADD(Final[Date],-1,YEAR)))
 
all works good but, when I am adding a slicer by Quarter, it tells me it expects a contigoous selection.
 
Any one has a clou on how I should fix it? 
 
 
thanks for the help.
Simone
1 ACCEPTED SOLUTION
edhans
Super User
Super User

Hi @simo12447 

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

9 REPLIES 9
edhans
Super User
Super User

Hi @simo12447 

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

man.. after 4 hours on this **bleep** formula... I got it!!!! thanks for the help. really killed me!!! appreciate!


@simo12447 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?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Here we go... I think I tried every possible combination of formulas in the last 5 hours! 😁
 
Inv. Bal. 20 = CLOSINGBALANCEQUARTER([Total SUM],(Final[Date]),Final[Type]="Inventory")
Inv. Bal. 19 = CALCULATE([Inv. Bal. 20],SAMEPERIODLASTYEAR('Calendar'[Date]))
 

thanks 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:

2020-05-21 10_05_10-Issue - Power BI Desktop.png

See this article near the bottom from SQLBI. It concludes the same thing I have. Emphasis mine.

 

"The STARTOFMONTHSTARTOFQUARTERSTARTOFYEARENDOFMONTHENDOFQUARTER, 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."



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
parry2k
Super User
Super User

@simo12447 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!






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

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it Out!

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors