Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
88 | |
82 | |
65 | |
64 | |
58 |
User | Count |
---|---|
171 | |
115 | |
109 | |
74 | |
69 |