Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

% Difference between Measures

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 betw...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
dax
Community Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      dax
      Community Support
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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?