Don't Sum if there is no Date value

[colourfullife]

Hi All,

i need your help.

i have this code below.

i don't want to sum PO Line Quantity if there is no dates in CDD

1. CDD =

VAR _End = CALCULATE ( MAX ( 'SPARES - PO STATUS'[CDD] ), ALL ( 'calendar' ))

RETURN

    IF (

        _End < MIN ( 'calendar'[Date] ),

        BLANK (),

CALCULATE(

    Sum('SPARES - PO STATUS'[PO_LINE_QUANTITY]), USERELATIONSHIP('SPARES - PO STATUS'[CDD],'calendar'[Date]),

    FILTER(

        ALLSELECTED('calendar'[Date]),

        ('calendar'[Date] <= MAX('calendar'[Date])))))

now the graph look funny as below.

any advice would be much appreciated.

thank you,

CL

[amitchandak]

@colourfullife , not very clear, Try two modification

CDD =
VAR _End = CALCULATE ( MAX ( 'SPARES - PO STATUS'[CDD] ), ALL ( 'calendar' ), USERELATIONSHIP('SPARES - PO STATUS'[CDD],'calendar'[Date]))
RETURN
IF (
_End < MIN ( 'calendar'[Date] ) || isblank(MAX ( 'SPARES - PO STATUS'[CDD] )),
BLANK (),
CALCULATE(
Sum('SPARES - PO STATUS'[PO_LINE_QUANTITY]), USERELATIONSHIP('SPARES - PO STATUS'[CDD],'calendar'[Date]),
FILTER(
ALLSELECTED('calendar'[Date]),
('calendar'[Date] <= MAX('calendar'[Date])))))

or

CDD =
VAR _End = CALCULATE ( MAX ( 'SPARES - PO STATUS'[CDD] ), USERELATIONSHIP('SPARES - PO STATUS'[CDD],'calendar'[Date]))
RETURN
IF (
_End < MIN ( 'calendar'[Date] ) || isblank(MAX ( 'SPARES - PO STATUS'[CDD] )),
BLANK (),
CALCULATE(
Sum('SPARES - PO STATUS'[PO_LINE_QUANTITY]), USERELATIONSHIP('SPARES - PO STATUS'[CDD],'calendar'[Date]),
FILTER(
ALLSELECTED('calendar'[Date]),
('calendar'[Date] <= MAX('calendar'[Date])))))

[colourfullife]

thanks for your reply

i got this result with both codes

[v-shex-msft]

Hi @colourfullife,

Maybe you can break the relationship between fact table and calendar, then you can use calendar date as axis with the following measure formula as value:

CDD =
VAR _End =
    MAXX ( ALL ( 'SPARES - PO STATUS' ), 'SPARES - PO STATUS'[CDD] )
VAR currDate =
    MAX ( 'calendar'[Date] )
RETURN
    IF (
        currDate <= _End,
        CALCULATE (
            SUM ( 'SPARES - PO STATUS'[PO_LINE_QUANTITY] ),
            FILTER (
                ALLSELECTED ( 'SPARES - PO STATUS' ),
                'SPARES - PO STATUS'[CDD] <= MAX ( 'calendar'[Date] )
            )
        )
    )

If above not help, please provide some dummy data with raw table structure then we can do test to coding formula.

How to Get Your Question Answered Quickly 
Regards,

Xiaoxin Sheng

[colourfullife]

Hi,

i think it's not working as i want.

attached link for dummy file.

dummy file

thank you,

CL

[v-shex-msft]

Hi @colourfullife,

I can't open the sample file on my side, it should the file probably be encrypted or corrupted. Can you please fix this?

Regards,

Xiaoxin Sheng

[colourfullife]

Hi @v-shex-msft  

please check this one : LINK 

Regards,

CL

[v-shex-msft]

Hi @colourfullife,

So you mean you only want to show the data point without any aggregate if fact table 'cod' fields do not exist on the calendar table? If this is a case, you can remove '<' operator to only display the data point on correspond calendar dates.

CDD =
VAR _End =
    MAXX ( ALL ( 'SPARES - PO STATUS' ), 'SPARES - PO STATUS'[CDD] )
VAR currDate =
    MAX ( 'calendar'[Date] )
RETURN
    IF (
        currDate <= _End,
        CALCULATE (
            SUM ( 'SPARES - PO STATUS'[PO_LINE_QUANTITY] ),
            FILTER (
                ALLSELECTED ( 'SPARES - PO STATUS' ),
                'SPARES - PO STATUS'[CDD] = MAX ( 'calendar'[Date] )
            )
        )
    )

Regards,

Xiaoxin Sheng