Forum Discussion

SJ25's avatar
SJ25
Resolver I
4 years ago

If statement not working after publishing

I have a simple calculated column as mentioned below:

Status_1 
IF(Table[MaxDate] = BLANK(), "Uncf",
IF(Today() > Table[MaxDate], "PD", "Not PD"))
 
I use this to create a simple table showing the status and count of each status. This works correctly on the desktop file, but the moment I publish it and the data gets refreshed (through gateway connection) it gives me the wrong value for the "PD" status. For some reason it just does not recognize or calculate the "Not OD" part and just splits the data into "Uncf" & "PD" thereby exaggerating the value for "PD". I have tried creating this measure in power query, creating a variable and then using that in the statement and a few other things too but nothing seems to work. I am at a loss for this simple stuff not working. Any help would be much appreciated.


Thanks!



Need Help

4 Replies

  • Shishir22's avatar
    Shishir22
    Solution Sage
    4 years ago

    Hello SJ25, 

     

    This might happen because Power BI server may be situated in other then your zone. Can you please try creating a measure as today() just to check what date is coming.

     

    I would suggest to use UTC time and convert it into your timezone and then try to check.

     

    Please accept it as a solution if it solves your issue. Kudos are also accepted!

     

    Thanks,

    Shishir

     

     

  • SJ25's avatar
    SJ25
    Resolver I
    4 years ago

    Hey Shishir, I tried that and the date is showing as 24/3 which is correct. I also checked to see if the date formate for MaxDate is dd/mm/yyyy. There are 5700 rows in Table and now the report is showing all of them under "Uncf" status and blank for the other two categories. 

  • SJ25's avatar
    SJ25
    Resolver I
    4 years ago

    I am using List.Max in power query to get the MaxDate, and I am thinking that's where it's going wrong when the report is published but I don't know what else to try. 

    Edit: I tried to create another calculated column to get the max date, still no difference. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi SJ25 ,

     

    Power BI Service uses UTC time, please add or subtract the corresponding time difference in your formula and check again.

    An hour = 1/24

     

    Best Regards,

    Jay