Forum Discussion

Heonsang's avatar
Heonsang
Frequent Visitor
7 years ago
Solved

Find out first date workable in slicer

Hello,

 

I'm trying to calculate premier and repeat value from the data table. Last time I could create Premier data by adding the measure CALCULATE(MIN(Data[Transmitted Date]), VALUES(Data[Program])) but this isn't useless at this scenario. If I add Transmitted Date in Result Table then this measure doesn't work. 

 

I have created the source and result I expect.

 

- Data Table

 

Program | Duration | Transmitted Date (d/m/y)

Avengers | 0:22:00 | 5/2/2018

Avengers | 0:22:00 | 2/3/2018

Avengers | 0:22:00 | 25/3/2018

Avengers | 0:22:00 | 26/3/2018

Avengers | 0:22:00 | 5/4/2018

Black Panther | 0:25:00 | 2/3/2018

Black Panther | 0:25:00 | 6/3/2018

Captain Marvel | 0:32:00 |7/2/2018

Captain Marvel | 0:32:00 |25/2/2018

 

 

- Report

Slicer : Tramsmitted Date is between 1/3/2018 and 31/3/2019

 

Result table

Program | Duration | Transmitted Date (d/m/y) | Premier or Repeat

Avengers | 0:22:00 | 2/3/2018 | Premier

Avengers | 0:22:00 | 25/3/2018 | Repeat

Avengers | 0:22:00 | 26/3/2018 | Repeat

Black Panther | 0:25:00 | 2/3/2018 | Premier

Black Panther | 0:25:00 | 6/3/2018 | Repeat

 

Premier duration = 0:22:00 + 0:25:00

 

 

 

Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

     

    Hi Heonsang

     

    I do not if i understood it correctly but that is what I did:

     

    created this model

     

     

     

     

     

    and added this measure:

     

    Measure = 
IF(
    HASONEVALUE( Programs[Program] ),
    IF(
        COUNTROWS( Transmissions ) > 0,
        VAR MinDte = 
        CALCULATE(
            MIN( Transmissions[TransmittedDate(d/m/y)] ),
            ALLSELECTED( 'Calendar' )
        )
        RETURN
        IF( MinDte = MIN( 'Calendar'[Date] ), "Premier", "Repeat" ),
    BLANK()
    )
)

2 Replies

  • LivioLanzo's avatar
    LivioLanzo
    Icon for Solution Sage rankSolution Sage
    7 years ago

     

    Hi Heonsang

     

    I do not if i understood it correctly but that is what I did:

     

    created this model

     

     

     

     

     

    and added this measure:

     

    Measure = 
IF(
    HASONEVALUE( Programs[Program] ),
    IF(
        COUNTROWS( Transmissions ) > 0,
        VAR MinDte = 
        CALCULATE(
            MIN( Transmissions[TransmittedDate(d/m/y)] ),
            ALLSELECTED( 'Calendar' )
        )
        RETURN
        IF( MinDte = MIN( 'Calendar'[Date] ), "Premier", "Repeat" ),
    BLANK()
    )
)