Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am totally new in Power BI.
I have a table similar as the one displayed at the end of this post (we'll call it "Table").
I am trying to calculate the growth of the sum of all Items for all IDs from one date to another, knowing that :
I have the followings measures / calculated columns already in place and working, that could help :
I am now trying to create a measure that sum the Items of the date previous to the current one, but all the solutions I applied were not working. Based on https://community.powerbi.com/t5/Desktop/Previous-date-values/m-p/134347 - I replaced the second calculated column in the post by a measure - I created the following measure, but I get empty as a result :
The next step I've in mind is to create a measure growth_nItems that would give me the growth of the items between two dates for all IDs :
Table :
ID | Items | Date |
a | 25 | 10/10/2022 |
b | 12 | 10/10/2022 |
c | 67 | 10/10/2022 |
a | 35 | 17/10/2022 |
b | 27 | 17/10/2022 |
c | 80 | 17/10/2022 |
a | 45 | 22/10/2022 |
b | 33 | 22/10/2022 |
c | 86 | 22/10/2022 |
Could you please help me making previous_nItems working ?
Solved! Go to Solution.
Hello,
Thank you @amitchandak and @v-yinliw-msft for your answers.
I finally found an other solution that suited more my needs.
I created a calculated column previous_Items for storing Items of each ID at previsous date, as the formula and the table below describes :
previous_Items =
VAR currentID = Table[ID]
VAR currentDate = Table[Date]
VAR previousDate = CALCULATE(
LASTDATE(Table[Date]),
FILTER(Table, Table[Date] < currentDate)
)
RETURN CALCULATE( MAX(Table[Items]),
FILTER(Table, Table[Date] = previousDate),
FILTER(Table, Table[ID] = currentID)
)
ID | Items | Date | prevous_Items |
a | 25 | 10/10/2022 | 12 |
b | 12 | 10/10/2022 | 67 |
c | 67 | 10/10/2022 |
|
a | 35 | 17/10/2022 | 27 |
b | 27 | 17/10/2022 | 80 |
c | 80 | 17/10/2022 |
|
a | 45 | 22/10/2022 | 33 |
b | 33 | 22/10/2022 | 86 |
c | 86 | 22/10/2022 |
|
I was then able to create the measure previous_nItems by summing the calculated column previous_Items.
previous_nItems = CALCULATE(SUM(Table[previous_Item]))
I also created a calculated column DoD_Items that calculates the growth of Items between two dates :
DoD_Items = DIVIDE(Table[Items] - Table[previous_Items], BMW[prevous_Items], 0)
Hello,
Thank you @amitchandak and @v-yinliw-msft for your answers.
I finally found an other solution that suited more my needs.
I created a calculated column previous_Items for storing Items of each ID at previsous date, as the formula and the table below describes :
previous_Items =
VAR currentID = Table[ID]
VAR currentDate = Table[Date]
VAR previousDate = CALCULATE(
LASTDATE(Table[Date]),
FILTER(Table, Table[Date] < currentDate)
)
RETURN CALCULATE( MAX(Table[Items]),
FILTER(Table, Table[Date] = previousDate),
FILTER(Table, Table[ID] = currentID)
)
ID | Items | Date | prevous_Items |
a | 25 | 10/10/2022 | 12 |
b | 12 | 10/10/2022 | 67 |
c | 67 | 10/10/2022 |
|
a | 35 | 17/10/2022 | 27 |
b | 27 | 17/10/2022 | 80 |
c | 80 | 17/10/2022 |
|
a | 45 | 22/10/2022 | 33 |
b | 33 | 22/10/2022 | 86 |
c | 86 | 22/10/2022 |
|
I was then able to create the measure previous_nItems by summing the calculated column previous_Items.
previous_nItems = CALCULATE(SUM(Table[previous_Item]))
I also created a calculated column DoD_Items that calculates the growth of Items between two dates :
DoD_Items = DIVIDE(Table[Items] - Table[previous_Items], BMW[prevous_Items], 0)
Hi @MaskedNico .
You can try this method:
Here is Sample data.
Due to the Sample data, I create a date table:
Then previous_nItems you can do like this:
Measure:
previous_nItems =
CALCULATE (
SUM ( 'Table'[Items] ),
FILTER (
'Table',
'Table'[Date]
= DATE ( YEAR ( RELATED ( 'Table 2'[Date] ) ), MONTH ( RELATED ( 'Table 2'[Date] ) ), DAY ( RELATED ( 'Table 2'[Date] ) ) )
)
)
If you want to know the previous data, you can select the previous date directly.
Is this what you expect?
Hope this helps you.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@MaskedNico , Try a measure like
Last Day Non Continuous = CALCULATE(Countrows(Table) ,filter(ALLSELECTED('Date'),'Date'[Date] =calculate( max(Table[ Date]), FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])))))
Date of table and date of date table is joined
User | Count |
---|---|
128 | |
108 | |
99 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |