Need to add two filters

[Anonymous]

Hello  -  I have a Measure that I need to add two cumulative totals.   One for remaining unshipped inventory in 2019, and the other for unshipped inventory in 2020.    The formula works perfectly if I just use "2020".    But my formula below does not work  (trying to add "2019".  

How to fix?    Thank you in advance datanauts!

YTD Backlog = CALCULATE (

    [Backlog Total],

    CALCULATETABLE(

    DATESYTD ( 'DateTable'[Date] ),

    DateTable[DatesWithShipments] = TRUE,FILTER(DateTable,DateTable[Year]="2020" && DateTable[Year]="2019")

)

)

[Icey]

Hi @Anonymous ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards
Icey

[Anonymous]

Hi Icey  - 

The problem was not solved in the forum but I found a solution myself.   

I don't think anyone properly understood what I wanted.    I simply needed a measure that filtered remaining 2019 unshipped orders, and 2020 ytd orders, and combined them into one total (sum of order value) that I could display on a card visual.  

I ended up creating two separate measures, then adding them together.    People kept trying to put TWO values on one card visual..and that is not what I wanted.    I wanted the sum of two years together.  

[Icey]

Hi @Anonymous ,

In card visual, it can only show one value. So, if you want to show several values, you need to create a measure.

I created a simple sample. Please check if it is what you want.

In my example, I create measures like so:

Sales Total = SUM ( financials[ Sales] )

YTD Backlog = CALCULATE (
    [Sales Total],
    DATESYTD ( 'DateTable'[Date] ))

YTD Measure 1 = 
CONCATENATEX (
    ADDCOLUMNS ( VALUES ( DateTable[Year] ), "YTD", [YTD Backlog] ),
    [YTD],
    "; "
)

YTD Measure 2 = SUMX ( financials, [YTD Backlog] )

 

For details, please check the attached PBIX file.

Best Regards,

Icey

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

[Anonymous]

This might be more complicated than what I am trying to do. 

I simply want to add YTD 2019 and YTD 2020 together in one measure?  I could of course create two separate measures, and then create a third that added these two together.   

But I am trying to do it all in one measure.  

[Icey]

Hi @Anonymous ,

Maybe I didn't explain clearly. Let me explain more clearly:

1. Create a sum measure, which in your scenario is called "[Backlog Total]".

Sales Total = SUM ( financials[ Sales] )

2. Create a YTD measure, which in your scenario is called "[YTD Backlog]".

YTD Backlog = CALCULATE (
    [Sales Total],
    DATESYTD ( 'DateTable'[Date] ))

3. Create another measure to show in a card, because card visual can only show one value.

YTD Measure 1 = 
CONCATENATEX (
    ADDCOLUMNS ( VALUES ( DateTable[Year] ), "YTD", [YTD Backlog] ),
    [YTD],
    "; "
)

Or, use this measure I created today:

YTD Measure 3 = 
CONCATENATEX (
    ADDCOLUMNS (
        VALUES ( DateTable[Year] ),
        "YTD", CONCATENATE ( [Year], CONCATENATE ( ": ", [YTD Backlog] ) )
    ),
    [YTD],
    "; "
)

For details, please check the attached PBIX file.

Best Regards,

Icey

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

[Gordonlilj]

Hi,

Try using || (OR) instead of && (AND)

YTD Backlog = CALCULATE (
    [Backlog Total],
    CALCULATETABLE(
    DATESYTD ( 'DateTable'[Date] ),
    DateTable[DatesWithShipments] = TRUE,FILTER(DateTable,DateTable[Year]="2020" || DateTable[Year]="2019")
)
)

[Anonymous]

Hi Gordon  -  The formula did not produce any errors...so that's good.   But it still is not working. 

You can see from below that the measure (which is on both visuals) is still showing only the 2020 value on the card visual.  

And you can see the filter is selected for both years.    Not sure why the formula is not working but it seems like it should?  

[Anonymous]

And just to clarify.   What I am looking for is to have the two years added together  (2019 + 2019).   So the YTD total name is a bit deceptive.  

I need the YTD total to still pull in the remaining balance of shipments that have not gone out in 2019.