Forum Discussion
Helper IIIcreating measure using dax
Since you already have a date table then no need for a Years table. Sorry for that but I did not have all the information about your data model.
Two relationshops have to be created. One "Active" between DateTable[Date] and Query[Basic fin.] and other "Inactive" between DateTable[Date] and Query[Out of Warranty]
The measure would be
Remaining Quanitiies of Product Still in Warrenty = CALCULATE ( SUM ( 'Query'[Del. qty] ), 'DateTable'[Year] >= MAX ( 'DateTable'[Year] ), USERELATIONSHIP ( 'Query'[Out of Warranty], 'DateTable'[Date] ) )
Community ChampionHi osama_ayoub
First of all the existing [Year] column represents the year of the [Basic fin.] date. You need to create a new [Out of Warranty Year] column which simply YEAR ( 'Table'[Out of Warranty] ).
In order to be able to see KPI's that are related to any of the year columns at the same time in the same visual, you need also to have a dimension 'Years' table and is simply a single column table that is linked with an active relationship with 'Table'[Year] and a non-active relationship with 'Table'[Out of Warranty Year]. Then you can place 'Years'[Year] column in a visual along with your measures.
Remaining Quanitiies of Product Still in Warrenty =
CALCULATE (
SUM ( 'Table'[Del. qty] ),
'Years'[Year] >= MAX ( 'Years'[Year] ),
USERELATIONSHIP ( 'Table'[Out of Warranty Year], 'Years'[Year] )
)
Helper IIII created a table (has all distinct years in query table) as a link between query table(which have my sales data) and calender table ,but I got this error