Current year vs prevous year

[perpor]

Hello

I have a dataset with the following 2 columns, daily data.

I am trying to add a new measure with the var of each day over the day in the previous year

many thanks

[v-shex-msft]

Hi @perpor,

You can try to use below formula(calculated column) to find out the previous year data. It will return blank if can't find the specific index value.

Previou = LOOKUPVALUE('Table'[Index],'Table'[Date],DATE(YEAR([Date])-1,MONTH([Date]),DAY([Date])))

Regards,

Xiaoxin Sheng

[perpor]

thanks a lot, I have now 'sintax error' on MONTH and I trying to understand why, change the format, eccc..

[perpor]

solved, with my local setting I must use ';' instead of ','.

But now I have the error:  'A table of multiple values was supplied where a single value was expexted' ...

[v-shex-msft]

Hi @perpor,

It sounds like your table contains duplicate records. Can you please share sample pbix file to test?

Regards,

Xiaoxin Sheng

[Anonymous]

You new measure could be:

Get Difference =

        var indexPY = CALCULATE(SUM([Index]),DATEADD([data],-1,YEAR))

        return ([index] - [indexPY] 

where [index] and [data] the columns of your data set. The [data] column should of 'Date' or 'Date/Time' type

[perpor]

thanks a lot

but I have always the error:  The syntax for 'YEAR' is incorrect, also trying with lowercase 'year'

maybe the [data] column is not seen correctly ? it comes from a sql server table defined as 'date' ...

[perpor]

trying a lot maybe I have understand, the syntax is not:

DATEADD(<dates>,<number_of_intervals>,<interval>)

like in the doc, but is :

DATEADD(<dates>;<number_of_intervals>;<interval>)

so it works....

[perpor]

with the ';'  and not with the '.'

[perpor]

but maybe this is not the right way, I need a value for each line of the column 'Var year', so I obtain only a total value for all the lines.......

[perpor]

I think that these calculations are not feasible with DAX or are too complicated, and it's best to do the calcolations before to import in Power BI ....

[wwhittenton]

Hi, @perpor,

If I understand your problem, you're looking to subract last year's Index value from today's Index value. To do this:

1. change the Data Type of your date to "Date" by going to the Formatting section of the Modeling tab in Power BI Desktop.

2. use the following formula (I think):

Var year = DataTable[Index] - LOOKUPVALUE( DataTable[Index] , DataTable[data] , DATE( YEAR(DataTable[data] -1 ) , MONTH(DataTable[data] , DAY(DataTable[data]) )

note: If your region uses ";" rather than "," in the DAX syntax just replace those values in the formula above. Replace "DataTable" in the formula whatever your table is named.

[perpor]

thanks wwhittento for the advice, I made several attempts but without success, I continue to think that Dax is a language too complicated to be used in a context like what I need

[Zubair_Muhammad]

Hi @perpor

Try this measure

=
SUM ( TableName[Index] )
    - CALCULATE (
        SUM ( TableName[Index] ),
        SAMEPERIODLASTYEAR ( VALUES ( TableName[Data] ) )
    )

[perpor]

thanks Zubair_Muhammad, but this gives only the total of the year, I need an entire serie of data, day by day...

[Zubair_Muhammad]

@perpor

Did you add it as a calculated column?

Actually i wrote a MEASURE

[perpor]

with New Measure I obtain a single value, with New Column, a series of data..

[Anonymous]

I've always used a ',' and it worked for me:

https://msdn.microsoft.com/en-us/library/ee634905.aspx

[Anonymous]

Do you have dates that are not appropriate? Try sorting your column to see min and max values. There might be something not correct there.