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
Anonymous
Not applicable

COMPARE CURRENT MONTH PRICE VS CUMULATIVE LAST YEAR

Hi

 

I´m looking the way to compare my current month price vs last year cumulative final price, the calculation is something like this:

 

ITEMMONTHMONTH PRICEAVG PRICEDIF 
ITEM 1oct-1755  
ITEM 2oct-172020  
ITEM 3oct-178080  
ITEM 1nov-17107.5  
ITEM 2nov-173326.5  
ITEM 3nov-178582.5  
ITEM 1dic-1777.3  
ITEM 2dic-172526.0  
ITEM 3dic-178081.7  
ITEM 1ene-18111150% current ene-18 price vs YTD average price of 2017)
ITEM 2ene-182222-15% current ene-18 price vs YTD average price of 2017)
ITEM 3ene-18909010% current ene-18 price vs YTD average price of 2017)
ITEM 1feb-1847.52% current feb-18 price vs YTD average price of 2017)
ITEM 2feb-181518.5-29% current feb-18 price vs YTD average price of 2017)
ITEM 3feb-189190.511% current feb-18 price vs YTD average price of 2017)

 

At the end I want to show a matrix with the item lista and their difference

 

 JAN 18feb-18
 DIF CURRENT MONTH VS AVERAGE PRICE OF ALL 2017DIF CURRENT MONTH VS AVERAGE PRICE OF ALL 2017
ITEM 150%2%
ITEM 2-15%-29%
ITEM 310%11%

 

 

Thank you

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @Anonymous ,

 

You can try to use following measure formula to compare current amount with previous YTD average:

Diff =
VAR currDate =
    MAX ( Table[Month] )
VAR currPrice =
    CALCULATE (
        SUM ( Table[Month Price] ),
        ALLSELECTED ( Table ),
        VALUES ( Table[Item] ),
        VALUES ( Table[Month] )
    )
VAR PrevYTD =
    CALCULATE (
        AVERAGE ( Table[Month Price] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( Table[Month] )
                = YEAR ( currDate ) - 1
                && Table[Month]
                    <= DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) )
        ),
        VALUES ( Table[Item] )
    )
RETURN
    currPrice - prevYTD


Notice: I still not so sure for detail compare calculation, you can modify bold part with your own calculation formulas.


Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

HI @Anonymous ,

 

You can try to use following measure formula to compare current amount with previous YTD average:

Diff =
VAR currDate =
    MAX ( Table[Month] )
VAR currPrice =
    CALCULATE (
        SUM ( Table[Month Price] ),
        ALLSELECTED ( Table ),
        VALUES ( Table[Item] ),
        VALUES ( Table[Month] )
    )
VAR PrevYTD =
    CALCULATE (
        AVERAGE ( Table[Month Price] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( Table[Month] )
                = YEAR ( currDate ) - 1
                && Table[Month]
                    <= DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) )
        ),
        VALUES ( Table[Item] )
    )
RETURN
    currPrice - prevYTD


Notice: I still not so sure for detail compare calculation, you can modify bold part with your own calculation formulas.


Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.