Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

relative date

Hello, 

 

I got an inventroy table like this, where I have the elaboration and expiry date for some products, I want to keep track of the number of expired products products and $ lost each week, I need your help in caclulating a measure to view expired products previous months and next months with a slicer, relative date

Thanks

Need Help
Show and Tell
Tips and Tricks

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous,

     

    Here I made one sample for your reference.

     

    1. Enter the data and create a dimtime table.

     

     

    dimtime = CALENDARAUTO()

     

     

    2. Create the measures as below.

     

     

    next month J = IF(MONTH(SELECTEDVALUE(dimtime[Date]))<>12, IF(MAX(Table1[exp_date])<=DATE(YEAR(SELECTEDVALUE(dimtime[Date])),MONTH(SELECTEDVALUE(dimtime[Date]))+1,31) && MAX(Table1[exp_date])>=DATE(YEAR(SELECTEDVALUE(dimtime[Date])),MONTH(SELECTEDVALUE(dimtime[Date]))+1,1),1,0),IF(MAX(Table1[exp_date])<=DATE(YEAR(SELECTEDVALUE(dimtime[Date]))+1,1,31) && MAX(Table1[exp_date])>=DATE(YEAR(SELECTEDVALUE(dimtime[Date]))-1,1,1),1,0))
    Previous month J = IF(MONTH(SELECTEDVALUE(dimtime[Date]))<>1, IF(MAX(Table1[exp_date])<=DATE(YEAR(SELECTEDVALUE(dimtime[Date])),MONTH(SELECTEDVALUE(dimtime[Date]))-1,31) && MAX(Table1[exp_date])>=DATE(YEAR(SELECTEDVALUE(dimtime[Date])),MONTH(SELECTEDVALUE(dimtime[Date]))-1,1),1,0),IF(MAX(Table1[exp_date])<=DATE(YEAR(SELECTEDVALUE(dimtime[Date]))-1,12,31) && MAX(Table1[exp_date])>=DATE(YEAR(SELECTEDVALUE(dimtime[Date])),12,1),1,0))
    TOTAL AMOUNT = CALCULATE(MAX(Table1[cost])*MAX(Table1[amount]))

     

    3. Create visuals and make the tables visuals filterd by [next month J] and [Previous month J] seperatly. Then we can get the result as we excepted.

     

     

    For more details, please check the pbix as attached.

     

    https://www.dropbox.com/s/4wckydml9xe5bbx/relative%20date.pbix?dl=0

     

    Regards,

    Frank

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi Anonymous,

       

      Does that make sense? If so, kindly mark my answer as a solution to close the case.

       

      Regards,

      Frank

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Thanks v-frfei-msft but I think that I need a different aproach cause it didn't worked for me.

         

        If there is another waty to do so I will appreciatte

         

         

        Regards 

        Christian