Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Everyone, I have the following data and a need to make some operations from the months of year filtered vs December lastyear
Date | Year | YearMonth | Month | Day Term | Amount |
01/01/2020 | 2020 | 202001 | Jan | 7 | 100 |
01/01/2020 | 2020 | 202001 | Jan | 30 | 120 |
01/01/2020 | 2020 | 202001 | Jan | 45 | 104 |
01/01/2020 | 2020 | 202001 | Jan | 7 | 99 |
01/01/2020 | 2020 | 202001 | Jan | 30 | 93 |
01/01/2020 | 2020 | 202001 | Jan | 45 | 104 |
01/02/2020 | 2020 | 202002 | Feb | 7 | 94 |
01/02/2020 | 2020 | 202002 | Feb | 30 | 104 |
01/02/2020 | 2020 | 202002 | Feb | 45 | 106 |
01/02/2020 | 2020 | 202002 | Feb | 7 | 97 |
01/02/2020 | 2020 | 202002 | Feb | 30 | 90 |
01/02/2020 | 2020 | 202002 | Feb | 45 | 106 |
01/03/2020 | 2020 | 202003 | Mar | 7 | 105 |
01/03/2020 | 2020 | 202003 | Mar | 30 | 104 |
01/03/2020 | 2020 | 202003 | Mar | 45 | 101 |
01/03/2020 | 2020 | 202003 | Mar | 7 | 94 |
01/03/2020 | 2020 | 202003 | Mar | 30 | 91 |
01/03/2020 | 2020 | 202003 | Mar | 45 | 97 |
01/12/2019 | 2019 | 201912 | Dec | 7 | 88 |
01/12/2019 | 2019 | 201912 | Dec | 30 | 80 |
01/12/2019 | 2019 | 201912 | Dec | 45 | 92 |
01/12/2019 | 2019 | 201912 | Dec | 7 | 87 |
01/12/2019 | 2019 | 201912 | Dec | 30 | 90 |
01/12/2019 | 2019 | 201912 | Dec | 45 | 91
|
With these values I get the next result in Power BI
Values from Decembre 2019 I need them on every month in actual year (or filtered Year)
On the red boxes there's no problem, Amount total from December (2019) is on every month in 2020.
But in green and blue boxes I have the Average Day Term 27.58 on December but is not the same value on months in 2020.
To get to "Avg Day Term" I have the next measures
"Avg Day Term LY" is calculated
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi, @javr
You may try the following measures. The pbix file is attached in the end.
Avg Day Term LY =
VAR yr=SELECTEDVALUE(Data[Year])-1
return
SUMX(
FILTER(
SUMMARIZE(
ALL(Data),
[YearMonth],
"Result",
[Avg Day Term]
),
[YearMonth]=yr*100+12
),
[Result]
)
Avg Day Term LY 2 =
VAR yr=SELECTEDVALUE(Data[Year])-1
return
SUMX(
FILTER(
SUMMARIZE(
ALL(Data),
[YearMonth],
"Result",
[Avg Day Term 2]
),
[YearMonth]=yr*100+12
),
[Result]
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
You may download my PBI file from here.
Hope this helps.
You are welcome.
You should be using a calendar table
https://exceleratorbi.com.au/power-pivot-calendar-tables/
https://exceleratorbi.com.au/dax-time-intelligence-beginners/
Thanks @MattAllington, I didn't know this site, the links you send me really help me to understand thing i didn't know and i saw on the blog some very interesting post
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |