Forum Discussion
Helper IIdelta % sales
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
Resolver IIThis 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
jeoosmaFirst 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
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.