Forum Discussion

tina345's avatar
tina345
Icon for Helper II rankHelper II
5 years ago
Solved

Visualization for expiration date for a product

Hello, I have a data set like below. I am trying to visualize using %remaining for expiry from today's date for each product. Is there any way to visualize this or any better way beside   produc...
Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    Hi, tina345 

    According to your error message, I think the reason is that you have to use a data summarization function for the columns referred to in the DATEDIFF() function because you are using a measure, you can try to change the DAX formula like this:

    Days_To_Expire = Calculate(DATEDIFF(TODAY(),MAX(SFP[PASS_EXPIRING_DATE]),DAY))

     

    If you still have a problem, you can post some sample data(without sensitive data) and your expected result.

    How to Get Your Question Answered Quickly 

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

vanessafvg's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
5 years ago

in that scenario you need to create a calculated column either created in power query or in dax to get the number of days, ie in dax

create column

ie Days to Expire =
CALCULATE ( DATEDIFF ( TODAY (), expirydate, DAY ) )

then you can create another colum with the banding

example,

Expiry Band =
SWITCH (
    TRUE,
    [days to expire] > 1
        && [days to expire] < 8, "1-7 days",
    [days to expire] > 8
        && [days to expire] < 15, "1-14 days",
    "unknown"

tina345's avatar
tina345
Icon for Helper II rankHelper II
5 years ago

Hi Venessafvg,

 For some reason while creating calculated column , I am getting this error, some expiry date on my table are nulls I hope its not because of that.Error says this is for measure, where as I am creating new calculating query.

 

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, tina345 

    According to your error message, I think the reason is that you have to use a data summarization function for the columns referred to in the DATEDIFF() function because you are using a measure, you can try to change the DAX formula like this:

    Days_To_Expire = Calculate(DATEDIFF(TODAY(),MAX(SFP[PASS_EXPIRING_DATE]),DAY))

     

    If you still have a problem, you can post some sample data(without sensitive data) and your expected result.

    How to Get Your Question Answered Quickly 

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.