Forum Discussion

jeoosma's avatar
jeoosma
Icon for Helper II rankHelper II
8 years ago
Solved

delta % sales

 

Hi guy. In this table I want to put instead of total the % of var between the two year. Fr example type  "S" the increase % of 2018 vs 2017. I don't know DAX and it's for me very uneasy to solve the problem. Thank you for help. Max.

Need Help
  • BKirsch12's avatar
    BKirsch12
    8 years ago

    This can be solved by creating 3 measures:

     

     

    Replace ** items with your fields.

     

    2017= CALCULATE(SUM(**your amount column**),FILTER('Budget/Expense',FIND("**2017**",'**Your Year Column**,,0)))

     

    2018= CALCULATE(SUM(**your amount column**),FILTER('Budget/Expense',FIND("**2018**",'**Your Year Column**,,0)))

     

     

    Once you create a measure, a measure can then take another measure as an input.

     

    % Difference = 2018 / 2017 - 1

     

    Hope that helps.

     

    Let me know if you have any questions

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Really need example source data that can be copied and pasted and information like whether or not you have a date table in your model. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

     

    That being said, you may want to look at my Quick Measure for Time Intelligence here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

     

    That measure shows Time Intelligence with YoY calculations "the easy way" and "the hard way".

  • BKirsch12's avatar
    BKirsch12
    Icon for Resolver II rankResolver II
    8 years ago

    This can be solved by creating 3 measures:

     

     

    Replace ** items with your fields.

     

    2017= CALCULATE(SUM(**your amount column**),FILTER('Budget/Expense',FIND("**2017**",'**Your Year Column**,,0)))

     

    2018= CALCULATE(SUM(**your amount column**),FILTER('Budget/Expense',FIND("**2018**",'**Your Year Column**,,0)))

     

     

    Once you create a measure, a measure can then take another measure as an input.

     

    % Difference = 2018 / 2017 - 1

     

    Hope that helps.

     

    Let me know if you have any questions

    • jeoosma's avatar
      jeoosma
      Icon for Helper II rankHelper II
      8 years ago

      First al all thank you.

      I have made this measure:

      2017 = CALCULATE(SUM([TotaleNetto]);FILTER('Vista Totale vendite';FIND("2017";[CdeAuftragK.DATA FATTURA];,0)))

       

      2018 = CALCULATE(SUM([TotaleNetto]);FILTER('Vista Totale vendite';FIND("2018";[CdeAuftragK.DATA FATTURA];,0)))

       

      % difference = 2018 / 2017 -1

       

      but when I select the fields I have an error msg:

       

      MdxScript(Model) (12,96

       

      Error Message:
      MdxScript(Model) (12, 96) Calculation error in measure 'Vista Totale vendite'[2017]: An argument of function 'FIND' has the wrong data type or has an invalid value.

       

       

      In the table I put on rows the fields "DATA FATTURA" with year and month; on columns the field "RABATTID", as value "NETTO TOTALE". So I have for every "RABATTID" a rows for year (I'm interested to 2017 and 2018). To have the same period I put the filter on month.

      What I want is the % difference of 2018 vs 2017. The measures you send me don't work (probably I'm wrong on syntax). Can you control (under there is all code)? Thank you....

       

      MAX.  ---->------>------>

       

       

      Source = #"Vista Totale vendite",
      #"Added Index" = Table.AddIndexColumn(Source, "Row Number" ,1),
      #"Kept Errors" = Table.SelectRowsWithErrors(#"Added Index", {"NUMERO ORDINE", "AuftragsArtId", "PosArtId", "COSTRUTTORE", "CODICE ARTICOLO", "ARTICOLO", "QUANTITA'", "RabattId", "LISTINO", "LISTINO TOTALE", "CreateDate", "NETTO TOTALE", "TotaleListino", "TotaleNetto", "CdeAuftragK.NUMERO ORDINE", "CdeAuftragK.TIPO", "CdeAuftragK.STATO", "CdeAuftragK.DATA FATTURA", "CdeAuftragK.CATEGORIA CLIENTE", "CdeAuftragK.CODICE CLIENTE", "CdeAuftragK.CLIENTE", "CdmKdBerater.CODICE VENDITORE", "CdmKdBerater.TIPO VENDITORE", "BaEmployee.VENDITORE", "Sconto"}),
      #"Reordered Columns" = Table.ReorderColumns(#"Kept Errors", {"Row Number", "NUMERO ORDINE", "AuftragsArtId", "PosArtId", "COSTRUTTORE", "CODICE ARTICOLO", "ARTICOLO", "QUANTITA'", "RabattId", "LISTINO", "LISTINO TOTALE", "CreateDate", "NETTO TOTALE", "TotaleListino", "TotaleNetto", "CdeAuftragK.NUMERO ORDINE", "CdeAuftragK.TIPO", "CdeAuftragK.STATO", "CdeAuftragK.DATA FATTURA", "CdeAuftragK.CATEGORIA CLIENTE", "CdeAuftragK.CODICE CLIENTE", "CdeAuftragK.CLIENTE", "CdmKdBerater.CODICE VENDITORE", "CdmKdBerater.TIPO VENDITORE", "BaEmployee.VENDITORE", "Sconto"})

      • BKirsch12's avatar
        BKirsch12
        Icon for Resolver II rankResolver II
        8 years ago

        This part of the error leads me to believe that there is a mismatch in the column you are trying to find 2017/2018.

         

        An argument of function 'FIND' has the wrong data type or has an invalid value.

         

        I would check the find argument, and make sure 2017 or 2018 is what you need. The 2017 or 2018 is looking for a direct character match in the lookup column you specified, so you'll need to reference a column that has the year in a yyyy format. If you don't have one, take your date column and create a calculated column. Syntax for that column is:

         

        Year = YEAR(** Your Data Column **)

         

        Also, for more advanced date functions, it can be useful to create a custom data table. This article is good at demonstrating the concept: https://www.mssqltips.com/sqlservertip/4857/creating-a-date-dimension-table-in-power-bi/

         

         

        Hope this helps, let me know.