Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Help With Date

Hi all,

 

I'm trying to compare a date measure with a date in my data table.  The date measure I have is:

 

IF(CALCULATE(SUM(YieldDataTable[RunQuantity]), FILTER(YieldDataTable,YieldDataTable[RunDate].[Date]=TODAY()))>100, TODAY(),
IF(CALCULATE(SUM(YieldDataTable[RunQuantity]), FILTER(YieldDataTable,YieldDataTable[RunDate].[Date]=TODAY()-1))>100, TODAY()-1,
IF(CALCULATE(SUM(YieldDataTable[RunQuantity]), FILTER(YieldDataTable,YieldDataTable[RunDate].[Date]=TODAY()-2))>100, TODAY()-2,
IF(CALCULATE(SUM(YieldDataTable[RunQuantity]), FILTER(YieldDataTable,YieldDataTable[RunDate].[Date]=TODAY()-3))>100, TODAY()-4,
BLANK())

 

Which works perfectly and does exactly what I want it to do.  However, when I try to use this date, it doesn't work any more:

 

Day Before Last Production Date =
IF(CALCULATE(SUM(YieldDataTable[RunQuantity]), FILTER(YieldDataTable,YieldDataTable[RunDate].[Date]=[Last Production Date]-1))>100, [Last Production Date],
IF(CALCULATE(SUM(YieldDataTable[RunQuantity]), FILTER(YieldDataTable,YieldDataTable[RunDate].[Date]=[Last Production Date]-2))>100, [Last Production Date]-2,
IF(CALCULATE(SUM(YieldDataTable[RunQuantity]), FILTER(YieldDataTable,YieldDataTable[RunDate].[Date]=[Last Production Date]-3))>100, [Last Production Date]-3,
IF(CALCULATE(SUM(YieldDataTable[RunQuantity]), FILTER(YieldDataTable,YieldDataTable[RunDate].[Date]=[Last Production Date]-4))>100, [Last Production Date]-4,
BLANK())

 

Any help is appreciated!

Need Help

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi Anonymous ,

     

    First of all the use of consecutive if's should be avoided due to the fact that you will get lost in the calculations and it weights on the measures, on your case believe that the problem is with the parenthisis that are not matching.

     

    For making this type of calculation you should apply SWITCH (that evaluates an expression against a list of values and returns one of multiple possible result expressions. documentation)

     

    Try to use the following rewritten measure:

    Day Before Last Production Date =
SWITCH (
    TRUE ();
    CALCULATE (
        SUM ( YieldDataTable[RunQuantity] );
        FILTER ( YieldDataTable; YieldDataTable[RunDate].[Date] = [Last Production Date] - 1 )
    ) > 100; [Last Production Date];
    CALCULATE (
        SUM ( YieldDataTable[RunQuantity] );
        FILTER ( YieldDataTable; YieldDataTable[RunDate].[Date] = [Last Production Date] - 2 )
    ) > 100; [Last Production Date] - 2;
    CALCULATE (
        SUM ( YieldDataTable[RunQuantity] );
        FILTER ( YieldDataTable; YieldDataTable[RunDate].[Date] = [Last Production Date] - 3 )
    ) > 100; [Last Production Date] - 3;
    CALCULATE (
        SUM ( YieldDataTable[RunQuantity] );
        FILTER ( YieldDataTable; YieldDataTable[RunDate].[Date] = [Last Production Date] - 4 )
    ) > 100; [Last Production Date] - 4;
    BLANK ()
)

    Regards,

    MFelix

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi, 

      Thanks for your reply.  However, I don't believe that was the problem as the measure still does not output anything.  

      The output to my last productiond date measure is 10/9/2019, hence the output to this measure should be 10/8/2019.  But it's currently returning blank.

       

      I believe it has something to do with how powerbi is comparing dates and how it cannot compare date from a measure to a date in the data table.  However, I don't exactly know why or how to fix it.

       

      Do you have any advice?

       

      Thanks.

      • MFelix's avatar
        MFelix
        Super User
        6 years ago

        Hi Anonymous ,

         

        Can you share a sample of your file and expected result? If information is sensitive can you do it trough private message?

         

        Regards,

        MFelix