Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Dear Community.
I am quite new to Power BI and I am Stuggeling a bit right now.
I would like to ask you for help.
I have a Table : Table 1 (That I will simplify here)
Date | Country | Sales (Running Total)
01.01.2019 Serbia 100
05.01.2019 Serbia 190
09.01.2019 Serbia 265
14.01.2019 Serbia 345
03.01.2019 Italy 110
06.01.2019 Italy 225
10.01.2019 Italy 355
13.09.2019 Italy 470
I would like to have a line for each day between 01.01.2019 ant 31.12.2019
For days with no Sales I would like to have the last amount of Sales known.
I have already prepared a table with all dates for each coutry : Table 2
Date | Country | Sales (Running Total)
01.01.2019 Serbia 100
02.01.2019 Serbia
03.01.2019 Serbia
04.01.2019 Serbia
05.01.2019 Serbia 190
06.01.2019 Serbia
07.01.2019 Serbia
08.01.2019 Serbia
09.01.2019 Serbia 265
10.01.2019 Serbia
11.01.2019 Serbia
12.01.2019 Serbia
13.01.2019 Serbia
14.01.2019 Serbia 345
01.01.2019 Italy
02.01.2019 Italy
03.01.2019 Italy 110
04.01.2019 Italy
05.01.2019 Italy
06.01.2019 Italy 225
07.01.2019 Italy
08.01.2019 Italy
09.01.2019 Italy
10.01.2019 Italy 355
11.01.2019 Italy
12.01.2019 Italy
13.01.2019 Italy 470
Now I would like to complete the empty lines.
I came to that :
MTD Sales = LOOKUPVALUE('Table1'[SALES]; 'Table1'[Country];Table2[Country]; 'Table1'[Date];maxx(FILTER('Table1';'Table1'[Date<=EARLIER(Table2[Date]));Table2[Date]))
This doesnt work cause in Table1 i actually have 52 country and therefore Sales for almost every day.
I wanted first to add something like
FILTER('Table1';'Table1'[Country]=Table2[Coutry])
But I don't know how to combine that to my lookup.
I hope that my post is understandable.
Looking forward to get a solution.
Thanks in advance.
Solved! Go to Solution.
Hi @AdrienP ,
We can create a calculated column as below.
Column = CALCULATE ( MAX ( 'Table 1'[Sales] ), FILTER ( 'Table 1', 'Table 1'[Date ] <= 'Table 2'[Date] && 'Table 1'[Country] = 'Table 2'[Country] ) )
Hi @AdrienP ,
We can create a calculated column as below.
Column = CALCULATE ( MAX ( 'Table 1'[Sales] ), FILTER ( 'Table 1', 'Table 1'[Date ] <= 'Table 2'[Date] && 'Table 1'[Country] = 'Table 2'[Country] ) )
Amazing thanks.
It is actually very easy like this.
Btw I wanted the value to start from 0 each month.
Therefore in Table 1 and Table 2 I have added a colum [Month] by extracting the month from the column [Date]
And then I've added the following line to the solution you gave me and it works fine.
&& 'Table1'[Month]=Table2[Month]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
80 | |
74 | |
66 |
User | Count |
---|---|
130 | |
106 | |
105 | |
86 | |
72 |