calculation not working

[ebrownretail]

Hi!

I am trying to pull what my next week forecast is, but the measure is not pulling through any information. Any ideas? As you can see in the screen shot it is summing but not filling in the rows. The sum is correct.

Sales Forecast =

(CALCULATE(SUM('Walmart Projection'[Unit Forecast]),

FILTER('Walmart Projection', 'Walmart Projection'[Walmart Week] =

CALCULATE(MAX(Dates[Walmart Week]),FILTER(Dates,Dates[Date] = TODAY())))))

[mostho]

@ebrownretail 

Do 'Walmart Projection'[Walmart Week] and Dates[Walmart Week] have the same type? I tried to reconstruct your example and it worked fine.

But at the end i guess you should tell us a little bit more about your data model.

[ebrownretail]

Hi!

Yes they are the same. Below are the tables i am using to input the information into Power BI. 

relationships - 

• Dates Table Walmart Week to Projections Walmart Week
• DC List Regional DC number to Projection Regional DC number
• DC List Regional DC number to DC Inv Regional DC number

I use a DC listing to get all the information by DC. as you can see below, it will sum the information, but not put it into each row. 

The dates table is just an extended dates table with a walmart week included. 

DC inventory

distribution_center_number	out_of_stock_case_quantity_this_year	warehouse_packs_shipped_to_store_this_year	OH	OO
6006	0	0	0	0
6009	0	0	0	0
6010	0	0	0	0
6011	0	0	0	0
6012	0	0	1	0
6016	0	0	0	0
6017	0	1	12	0
6018	0	0	0	0
6019	0	0	0	0
6020	0	0	0	0
6021	0	0	1	0

Projections. 

Regional DC	Store Number	Walmart Week	Unit Forecast
6094	121	202429	8
6068	1148	202429	19
7026	3620	202429	4
6066	262	202429	2
6031	4451	202429	21
6036	5673	202429	13
6092	2339	202429	1
7039	5438	202429	3
6054	4436	202429	19
6027	2129	202429	4
6066	684	202429	28
6054	510	202429	8
6016	3391	202429	6
7035	2026	202429	3
6030	2885	202429	3
7045	2809	202429	8
6048	1204	202429	16
7039	3434	202429	3
6070	1060	202429	11
6038	3221	202429	2
6048	521	202429	50
6054	6998	202429	2
6009	3734	202429	1

[mostho]

as i said i tried to reconstruct a minimal example and it worked.



maybe you can see on my pbix some difference to yours.

[ebrownretail]

Would you be able to paste the measure into the message? i can read it on the screenshot.

[mostho]

1. its the same as yours.
2. you can look in my pbix.

[ebrownretail · Accepted solution]

i couldnt get the file to open so that was why i was asking, but i just figured it out. Its because the porgram didnt like i had multiple many to many relationships to the dates table for the walmart week. I changed the one going to my past sales history to a single instead of both relationship and it works and keeps everything else working as well. 

[MattAllington]

I don't know what a 6 digit week is. If you want to successfully use Time Intelligence, there are many rules you must follow. There are different rules if you use a day level calendar and want to leverage in built time intelligence, and if you don't have a day level calendar, and want to write time based calculations. You have t shared any details about your calendar table or the way its joined, so I can't comment. You could read a couple of articles I wrote and I'm sure they will help you. 

https://exceleratorbi.com.au/dax-time-intelligence-beginners/

https://exceleratorbi.com.au/power-bi-calendar-tables/

[ebrownretail]

the 6 digit week is a year week combination. So this week is 202429 for the Walmart Week. 

[MattAllington]

I can't be sure without seeing you data model, but I can tell you that there is a problem with your FILTER statement

FILTER(Dates,Dates[Date] = TODAY())))))

the first parameter Dates inside your filter is already filtered by your visual. It looks like it is the entire Date table, but in fact it is a filtered copy of your date table. The first thing to try is this

FILTER(ALL(Dates),Dates[Date] = TODAY())))))

[ebrownretail]

Thanks. I added that and it did not change the issue.

I have a dates table that has 6 digit weeks on them

I have a projections table with 6 digit weeks

I am trying to get the table to bring back what the next weeks projection is by DC based on the 6 digit week number. Does this help?