Help required in Switch statement

[pbiforum123]

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"
)

[Icey]

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.

[pbiforum123]

I have already tried that it is not working as well.

[Icey]

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]

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

[Icey]

Hi @pbiforum123 ,

Is this problem solved?

Best Regards,

Icey

[pbiforum123]

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

[Icey]

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.

[pbiforum123]

Did you get Total Sales in the Date group? I don't see that in your screen shot! Sorry if I had missed anything.

[Icey]

Hi @pbiforum123 ,

No. As I said before, your expression contains duplicated dates. They can't show both the two text but only the one that appear first.

Best Regards,

Icey

[Icey]

Hi @pbiforum123 ,

Maybe this? Or, you can just create another column of "TotalSales".

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

Best Regards,

Icey

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

[pbiforum123]

Thanka for sharing this idea. Unfortunately I wont be Total Sales as seperate column, even if I do that I wont be able to sort it in the order I want.

[pbiforum123]

I understood! But our requirement is to show all the groups even though it is duplicate. Please let me know if that can be achieved in some way. Thanks

[Anonymous]

Hi @pbiforum123 ,

Please share the sample raw data and output.

Best Regards,
Mail2inba4

[pbiforum123]

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

[Anonymous]

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]

Thanks but you have passed wrong date here hence it was working...

Sample[Date] >= date(2019,9,1)

&& Sample[Date] <= date(2019,9,30), "SampleOlderThanThisMonth",

It should have date(1900,1,1)...

[pbiforum123]

As a quick fix I renamed the "Subtotal" to "Total Sales" 🙂

Only concern is I am not able to bring it to the top though I select Top in the subtotal settings.

[VasTg]

@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]

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