Worng value at December Last Year

[javr]

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

Min Days Term = MIN(Data[Day Term])

% = DIVIDE([Total Day Term], [Total Month])

Avg Day Term = sumx(summarize(Data,Data[Month], Data[Day Term],"_1", [Min Days Term] * [%]),[_1])

And to get "Avg Day Term 2" I used these measure 

CF Amount(Month) = SUM(Data[Amount])

Total % = DIVIDE([CF Amount(Month)],CALCULATE([CF Amount(Month)],ALL(Data[Day Term])))

Min Days Term = MIN(Data[Day Term])

Avg Day Term 2 = SUMX(VALUES(Data[Day Term]),[Total %]*[Min Days Term])

"Avg Day Term LY" is calculated

Avg Day Term LY =

         VAR yr=SELECTEDVALUE(Data[Year])-1

         return SUMX(FILTER(all(Data),Data[YearMonth]=(yr*100)+12),[Avg Day Term])

 

And "Avg Day Term LY 2" is similar

Avg Day Term LY 2 =

         VAR yr=SELECTEDVALUE(Data[Year])-1

         return SUMX(FILTER(all(Data),Data[YearMonth]=(yr*100)+12),[Total %]*[Min Days Term])

I expected to get on "Avg Day Term LY" and "Avg Day Term LY 2" the value for December (27.58) 

Do you know why I'm no getting thes value? Any ideas? 

 

Excel file as Source
https://app.box.com/s/a1my9hov8ttjnklc45cta06crbzzqjli

 

Pbix file

https://app.box.com/s/tw7ivrvkf95k6u3dpncmd9mxhfsrb3c1

 

 

Thanks in advance!

[v-alq-msft]

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.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[javr]

Thanks @Ashish_Mathur, this solution help me a lot

[Ashish_Mathur]

You are welcome.

[MattAllington]

You should be using a calendar table

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

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

[javr]

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