Forum Discussion

bilalkhokar73's avatar
bilalkhokar73
Icon for Helper IV rankHelper IV
5 years ago
Solved

After Publish DAX Error

SALES ORDER = CALCULATE(SUM(Sales[Amnt]),filter(Sales,Sales[ORDER_TYPE]="SALES_ORDER"),
FILTER(ALL(date),date[date]>=date(YEAR(TODAY()),MONTH(TODAY()),1) && date[date]<TODAY() ))

 

Dear team,
When I am wirting thsi formula in power query desktop then it is giigng me correct result as my NEEDED
but when I publish thsi report then error shwoing that contact support team
Can you please help me what wrong I have done here

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi bilalkhokar73 ,

     

    When you use the TODAY() function in the Power BI Desktop it uses your Local System time to determine this.

     

    When you upload your file to the Power BI Service, it used date from the server that hold your files .All the Power BI Servers time is set to UTC. So if you are in a timezone that is say -10 UTC, then due to this it is not today, but according to the Power BI Servers showing as tomorrow.

     

    You can alter your Today with -10 hours in the Query Editor:

     

    TodaysDate = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),-10)  

      

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

     

    Best Regards,

    Dedmon Dai

3 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    bilalkhokar73  I'm not sure what error you're getting, but you could simplify this expression if you have a DimDate table and use: 

     

    SALES ORDER = CALCULATE(SUM(Sales[Amnt]),filter(Sales,Sales[ORDER_TYPE]="SALES_ORDER"),
    DATESMTD('date'[date]) )

  • Kumail's avatar
    Kumail
    Icon for Impactful Individual rankImpactful Individual
    5 years ago

    Hello bilalkhokar73 

     

    If you could send sample.pbix that demonstrates what you are looking to get. It would really help to provide you a quick solution.

     

    You can send the sample .pbix file by adding it to your drive or dropbox and add the link here. 

     

    Regards
    Kumail Raza

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

    Hi bilalkhokar73 ,

     

    When you use the TODAY() function in the Power BI Desktop it uses your Local System time to determine this.

     

    When you upload your file to the Power BI Service, it used date from the server that hold your files .All the Power BI Servers time is set to UTC. So if you are in a timezone that is say -10 UTC, then due to this it is not today, but according to the Power BI Servers showing as tomorrow.

     

    You can alter your Today with -10 hours in the Query Editor:

     

    TodaysDate = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),-10)  

      

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

     

    Best Regards,

    Dedmon Dai