How to calculate a field measure based on conditions of another field and date ranges?

[RuthMerchán]

Hi everyone!

I have a table with 4 fields, date, status, product and liters. I have related this table with a dimension of date with date field.

I would like to get the following. 

Assuming that the date range selected is from 09/01/2023 to 09/20/2023:

- If the status=A then show only the result for the min date of the selected date range, I mean show just the liters for 09/01/2023

- if the status= B o C show the sum of liters for all the dates between the selected date range.

- if the status=C show the sum of liters for the max date of the selected date range + 1 day, I mean show just the liters for 09/21/2023

According to this, for status A should be 50 liters, for status B should be 114, status C 95 and status D 41 liters.

I tried to create a flag like this: 

Flag =

VAR StartDate = MIN(DIM_TIEMPO[Fecha])

VAR EndDate =EDATE(MAX(Dim_Tiempo[Fecha]), 1))

RETURN

IF(

    [Estado] IN {"B", "C"}, 1,

    IF(

        [Estado] = "A" && [Fecha] = StartDate, 1,

        IF(

            [Estado] = "D" && [Fecha] = EndDate, 1, 0

        )

    )

)

But it does not work. I have also tried creating 3 measures, like this:

Estado_A = CALCULATE(SUM(Sheet1[Litros]), Sheet1[Estado] = "A" && Sheet1[Fecha] = MIN(Dim_Tiempo[Fecha]))

Estado_B_C = CALCULATE(SUM(Sheet1[Litros]), Sheet1[Estado] IN {"B", "C"} && Sheet1[Fecha] >= MIN(Dim_Tiempo[Fecha]) && Sheet1[Fecha] <= MAX(Dim_Tiempo[Fecha]))

Estado_D = CALCULATE(SUM(Sheet1[Litros]), Sheet1[Estado]= "D" && Sheet1[Fecha]= EDATE(MAX(Dim_Tiempo[Fecha]), 1))

However Estado_D does not work... Furthermore, I would like to get this in just one column, no three...

The desired result is the following:

Is it possible? Please, could you help me to improve this solution or find another one that fits better?

Thank you very much for your support!

[RuthMerchán]

Hi! I finally got it 🙂

The solution was to create two tables more of time with date field each one (dim_tiempo1 and dim_tiempo2). Those tables are not related to nothing.

I have created two filters, one with the date of dim_tiempo1 and another filter with the date of dim_tiempo2. 

On the other hand, I changed the formula for literslike this:

Volume (L) =

VAR MinDate = MIN('DIM_TIEMPO1’[FECHA_ID])

VAR MaxDate = MAX('DIM_TIEMPO2’[FECHA_ID])

VAR MaxDatePlus1 = MaxDate + 1

RETURN

SUMX(

    FILTER(

        FACT_TABLE,  

        FACT_TABLE[Estado] = "A" && FACT_TABLE[Fecha]= MinDate ||

 FACT_TABLE [Estado] IN {"B","C"} && (FACT_TABLE[Fecha]>= MinDate && FACT_TABLE[Fecha]<= MaxDate)  ||

        FACT_TABLE[Estado] = "D" && FACT_TABLE[Fecha]= MaxDatePlus1

    ),

    FACT_TABLE[Litros]

)

that's all 🙂

Thank you!

[amitchandak]

@RuthMerchán , Create following measures

M1=
var _max = minx(allselected(table), Table[status] = "A"), Table[Date])
return
calculate(Sum(Table[liters]), Filter( table, Table[Date] =_max && Table[status] = "A"))

M2= calculate(Sum(Table[liters]), Filter( table, Table[status] in {"B", "C"}))

Sum Measure= Max(Values(Table[Status]), [M1] +[M2])

[RuthMerchán]

Hi! @amitchandak! 

I have tried and finaly when I put the Sum Measure give this error:

Do you have some idea how can I make the measure to obtain the status D?

Thank you very much!