% Difference between Measures

[Anonymous]

Hi guys,

im trying to calculate simple way difference between tow rates or even more but I dont find the proper way to do it. Here is the thing:

Id like to calculate differences between measures without to build a specific calculate measures for every year, I just want  to have % differences that calculate automatically that differences, for Net Sales, for Net Sales Avg and Qty Distribution (3 measures)

Its seems easy but i dont find the right way to do it

Best regards

[Anonymous]

Sorry I am not quite following what you need to do.

Could you provide a screenshot of what it would look like in Excel for excample?

Thank you

[Anonymous]

anybody can help me? I want to have a formul that generate automatically filter without to put the year

something like this

% Differences net sales= calculate(sum(netsales);filter(dates; year=year automatically))

Now I must créate net sales 2018, net sales 2019 also build % differences net sales 2018, % differences net sales 2019 

[dax]

Hi edumen, 

I am not clear about your requirement, did you mean you want to get difference between current year and previous year ? Or other year? If possible, could you please inform me the formula and expected output, then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Yes, that's correct, I want to know how can I create % differences between sales per each year.

I dont know how can I made it, just I know putting each year, look this:

Net Sales Current = CALCULATE(SUM([Net Sales]);FILTER('Detalles Fechas';'Detalles Fechas'[Año Fiscal]="FY 2019"))

Net Sales LY = CALCULATE(SUM([Net Sales]);FILTER('Detalles Fechas';'Detalles Fechas'[Año Fiscal]="FY 2018"))

% Dif Net = IFERROR(([Net Sales Current]-[Net Sales LY])/[Net Sales LY];BLANK())

I dont want to put the year in each formul, I would like to get something automatically if it'd be possible..

[dax]

Hi edumen, 

You could try  to add a year column in data model like below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcotUMDIwNFPSUTIyUIrVgQmYAwUMTZEELIACZsgqLIECpkCBWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"FY YAER" = _t, sales = _t]),
    #"Inserted Text After Delimiter" = Table.AddColumn(Source, "Text After Delimiter", each Text.AfterDelimiter([FY YAER], " "), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text After Delimiter",{{"Text After Delimiter", "year"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"year", Int64.Type}})
in
    #"Changed Type"

 Then create a measure like below(use filter in measure to refer to previous value)

Measure =
 (
    CALCULATE ( SUM ( T2[sales] ) )
        - CALCULATE (
            SUM ( T2[sales] ),
            FILTER ( ALL ( T2 ), T2[year] = MIN ( T2[year] ) - 1 )
        )
)
    / CALCULATE ( SUM ( T2[sales] ) )

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I dont understand very well about json option but trying to follow your example getting this

% = CALCULATE(sum('Ratios y Metricas'[Importe Neto Rappel Generico]))-CALCULATE(sum('Ratios y Metricas'[Importe Neto Rappel Generico]);FILTER(ALL('Ratios y Metricas'[Año]);'Ratios y Metricas'[Año]=MIN('Ratios y Metricas'[Año])-1))/CALCULATE(SUM('Ratios y Metricas'[Importe Neto Rappel Generico]))

What do I made wrong?

[dax]

Hi edume,

Yes, you could use calculated column to create column which is similar to my M code. You problem is that you miss the () in expression. You could try below expression 

% =
 (
    CALCULATE ( SUM ( 'Ratios y Metricas'[Importe Neto Rappel Generico] ) )
        - CALCULATE (
            SUM ( 'Ratios y Metricas'[Importe Neto Rappel Generico] );
            FILTER (
                ALL ( 'Ratios y Metricas'[Año] );
                'Ratios y Metricas'[Año]
                    = MIN ( 'Ratios y Metricas'[Año] ) - 1
            )
        )
)
    / CALCULATE ( SUM ( 'Ratios y Metricas'[Importe Neto Rappel Generico] ) )

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

It doenst work

being honest I dont understand how something seems so easy such as "% differences between figures" finally is so complex to do.

[Anonymous]

Hey

First I would split the fiscal year field or create another one from that one with just the number (stored as a number).

Then I would create a measure as following (just a guide, adapt so it takes your fields and tables):

measure =
var fiscalyear = SELECTEDVALUE(FY_number)
var currentYear = CALCULATE(SUM('ratios y metricas'[importe neto]), FY_number = fiscalyear)
var previousYear = CALCULATE(SUM('ratios y metricas'[importe neto]), FY_number = (fiscalyear - 1))
var result = DIVIDE(currentYear, previousYear, BLANK())
return result

[dax]

Hi edumen, 

It seems that it is cuased by your sample structure, so if possible, could you please inform me your sample data or your pbix file? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Sure.

I need this but I want to create a formul that calculate % differences automatically

Net sales, Net Sales Avg and Qty Clientes are sum's and distinct count without to be a specific year.