Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
javr
Regular Visitor

Worng value at December Last Year

Hello Everyone, I have the following data and a need to make some operations from the months of year filtered vs December lastyear

 

DateYearYearMonthMonthDay TermAmount
01/01/20202020202001Jan7100
01/01/20202020202001Jan30120
01/01/20202020202001Jan45104
01/01/20202020202001Jan799
01/01/20202020202001Jan3093
01/01/20202020202001Jan45104
01/02/20202020202002Feb794
01/02/20202020202002Feb30104
01/02/20202020202002Feb45106
01/02/20202020202002Feb797
01/02/20202020202002Feb3090
01/02/20202020202002Feb45106
01/03/20202020202003Mar7105
01/03/20202020202003Mar30104
01/03/20202020202003Mar45101
01/03/20202020202003Mar794
01/03/20202020202003Mar3091
01/03/20202020202003Mar4597
01/12/20192019201912Dec788
01/12/20192019201912Dec3080
01/12/20192019201912Dec4592
01/12/20192019201912Dec787
01/12/20192019201912Dec3090
01/12/20192019201912Dec4591

 

 

 

 

 

With these values I get the next result in Power BI

Report.png

 

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? 
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
v-alq-msft
Community Support
Community Support

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:

a1.png

 

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
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks @Ashish_Mathur, this solution help me a lot

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

You should be using a calendar table

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

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.