Forum Discussion

pbiforum123's avatar
pbiforum123
Post Patron
6 years ago

Help required in Switch statement

Since the first statement for "Total Sales" consists of the dates rest of the statement is not executed. If I remove the first statement which is
"Total Sales" rest of the statement is executed. What change I should be doing to make sure that I get all the statement. Please help

 

Date_Group =
SWITCH (
TRUE (),
Sales[Date] >= date(1900,1,1)
&& Sales[Date] <= date(2900,12,31), "Total Sales",
Sales[Date] >= date(1900,1,1)
&& Sales[Date] <= date(2019,9,30), "SalesOlderThanThisMonth",
Sales[Date] >= date(2019,9,30)
&& Sales[Date] <= date(2019,10,31), "SalesThisMonth",
Sales[Date] >= date(2019,10,31)
&& Sales[Date] <= date(2019,11,30), "SalesNextMonth",
Sales[Date] >= date(2019,11,30)
&& Sales[Date] <= date(2900,12,31), "SalesFuture"
)

Need Help

19 Replies

  • VasTg's avatar
    VasTg
    Memorable Member
    6 years ago

    pbiforum123 

     

    The execution of SWITCH statements are sequential and the execution will exit at the condition matches and the rest of the statments are ignored.

     

    Are you expecting to get two values for Date Group?

    "Total Sales" plus any one of  these values....."SalesOlderThanThisMonth","SalesThisMonth","SalesNextMonth","SalesFuture"

     

     

     

     

     

    • pbiforum123's avatar
      pbiforum123
      Post Patron
      6 years ago

      Thanks for responding!

       

      Exactly this is what I was trying to say. Execution gets exits as soon as it matches the first statement as it contains all the dates. In my requirement I am expecting to show all the below values not just 2. Please help me out on this. Let me know if you have any more questions.


      Total Sales
      SalesOlderThanThisMonth
      SalesThisMonth
      SalesNextMonth
      SalesFuture

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi pbiforum123 ,

    Please share the sample raw data and output.

     

    Best Regards,
    Mail2inba4

    • pbiforum123's avatar
      pbiforum123
      Post Patron
      6 years ago

      Since it has confidential data it cannot be shared. Sorry!

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi pbiforum123 ,

        Yeah, I understood no problem.
        I just gave some sample records and use the below dax for your request.

        Date_Group =
        SWITCH (
        TRUE (),
        Sample[Date] >= date(2019,9,1)
        && Sample[Date] <= date(2019,9,30), "SampleOlderThanThisMonth",
        Sample[Date] >= date(2019,9,30)
        && Sample[Date] <= date(2019,10,31), "SampleThisMonth",
        Sample[Date] >= date(2019,10,31)
        && Sample[Date] <= date(2019,11,30), "SampleNextMonth",
        Sample[Date] >= date(2019,11,30)
        && Sample[Date] <= date(2900,12,31), "SampleFuture",
        Sample[Date] >= date(1900,1,1)
        && Sample[Date] <= date(2900,12,31), "Total Sample"
        )
        Output:
         
        Best Regards,
        Mail2inba4

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    • pbiforum123's avatar
      pbiforum123
      Post Patron
      6 years ago

      Still not resolved! Currently I have done the temporary fix same as mentioned above...

      • Icey's avatar
        Icey
        Community Support
        6 years ago

        Hi pbiforum123 ,

        But it works on my side.🤔   Can you show me what you want with an example?

         

        Best Regards,

        Icey

         

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

         

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi pbiforum123 ,

    Sales[Date] >= date(1900,1,1)
&& Sales[Date] <= date(2900,12,31), "Total Sales",
    Sales[Date] >= date(2019,11,30)
&& Sales[Date] <= date(2900,12,31), "SalesFuture"

    The two expressions‘ date ranges have duplicate parts. Why do you write like so?

     

    Best Regards,

    Icey

    • pbiforum123's avatar
      pbiforum123
      Post Patron
      6 years ago

      As date days one gives the total sales and other gives the future sales...

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi pbiforum123 ,

    What about this:

    Date_Group =
SWITCH (
    TRUE (),
    Sales[Date] >= DATE ( 1900, 1, 1 )
        && Sales[Date] <= DATE ( 2019, 9, 30 ), "SalesOlderThanThisMonth",
    Sales[Date] >= DATE ( 2019, 9, 30 )
        && Sales[Date] <= DATE ( 2019, 10, 31 ), "SalesThisMonth",
    Sales[Date] >= DATE ( 2019, 10, 31 )
        && Sales[Date] <= DATE ( 2019, 11, 30 ), "SalesNextMonth",
    Sales[Date] >= DATE ( 2019, 11, 30 )
        && Sales[Date] <= DATE ( 2900, 12, 31 ), "SalesFuture",
    Sales[Date] >= DATE ( 1900, 1, 1 )
        && Sales[Date] <= DATE ( 2900, 12, 31 ), "Total Sales"
)

    Just put the expression of "Total Sales" to the last.

     

    Best Regards,

    Icey

     

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