Forum Discussion

osama_ayoub's avatar
osama_ayoub
Helper III
3 years ago
Solved

creating measure using dax

Hi,
I have a table of sales transaction with columns of product and quantity and date of sale and years of warrenty and column of date when this quantity will be out of warrenty and lastly status column which indicate if this quantity in warrenty or not based on today date and out of warrenty date
now I create table Visual in power bi and want to show years in rows and beside it I want to create measure to show with each year what is the remaining quanitiies of product still in warrenty
So if in 2023 there are 1000 unit of different products will be out of warrenty I want this measure to show sum of all the quantity still in warrenty (which will be out in 2024  &2025 &2026....etc)

thanks in advance

 

 

 

@Dax help
  • tamerj1's avatar
    tamerj1
    3 years ago

    osama_ayoub

    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] )
)

     

8 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi 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] )
)
    • osama_ayoub's avatar
      osama_ayoub
      Helper III
      3 years ago

      I 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 

       

       

       

    • osama_ayoub's avatar
      osama_ayoub
      Helper III
      2 years ago

       

      Hi Tamer,

      Thanks for your help

      I have Create 2 relationship

      one active relation between column(Date in Date Table) and (Date in my fact Table (Report)

      another relation (inactive) between (Date in Date Table) and (Out of Warrenty in my fact Table (Report))
      and have created measure like in the attached screenshot , and it seems it work fine but I should have a inreasing Trend in the start of seeling the products and then it shoud decline with years because in the first years of seeling the products it should be increasing quantity of products still in warrenty ?

      Regaeds

       

       

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    osama_ayoub

    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] )
)