Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
thanks a lot, I have now 'sintax error' on MONTH and I trying to understand why, change the format, eccc..
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' ...
Hi @perpor,
It sounds like your table contains duplicate records. Can you please share sample pbix file to test?
Regards,
Xiaoxin Sheng
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
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' ...
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....
with the ';' and not with the '.'
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.......
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 ....
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.
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
Hi @perpor
Try this measure
= SUM ( TableName[Index] ) - CALCULATE ( SUM ( TableName[Index] ), SAMEPERIODLASTYEAR ( VALUES ( TableName[Data] ) ) )
thanks Zubair_Muhammad, but this gives only the total of the year, I need an entire serie of data, day by day...
Did you add it as a calculated column?
Actually i wrote a MEASURE
with New Measure I obtain a single value, with New Column, a series of data..
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.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |