get ride of excess days in chart

[majdkaid22]

Hi guys,

I have created a cumulative calculated measure 

Measure = CALCULATE(SUM(meAccountTransaction[Deposits]),(FILTER(ALL(meCalendar[DateValue]),meCalendar[DateValue]<=MAX(meCalendar[DateValue]))))

Am using a Calender table which has dates until end of 2018, hence the line chart is showing all days of the month 

Is there any Date measure I can use to filter out the excess days (the future days/months/years) that I can create/apply?

Thanks,

Majd

[BhaveshPatel · Accepted solution]

In order for below measure to work, You need to create a date key column in both of your tables( meCalender and meAccountTransaction) as shown below and create the relationship based on this "DateKey" Column.

This will check the condition before evaluating the original measure of the cumulative total.

DateKey Column

Cumulative Total:=

IF (

    MIN ( meCalender[DateKey] )

        <= CALCULATE ( MAX ( meAccountTransaction[DateKey] ), ALL ( meAccountTransaction ) ),

    CALCULATE (

        SUM ( meAccountTransaction[Deposits] ),

        FILTER (

            ALL ( meCalender[DateValue] ),

            meCalender[DateValue] <= MAX ( meCalender[DateValue])

        )

    )

)

[majdkaid22]

Thanks @BhaveshPatel it worked as a treat. I found PostingDateID in the meAccountTransaction, and I did have Datekey in my calander!

one more question, if I want to create a Datekey for other tables I have, what formula should I use for a custom column to tranform a Date to DateKey in the same formart you stated in your earlier reply?

Thanks,

Majd

[BhaveshPatel]

Use this formula in Calculated Column to create a DateKey Column.

DateKey=meCalender[CalendarYear]*10000 + meCalender[Month Number]*100+DAY(meCalender[DateValue])