Create line graph for only Month end values based on date slicer

[manalla]

Hi, I have regualar transactional table where in data gets appended with every day load. Sample looks like below.

Every month last record would be last business working day. Like for June its 28th, July its 31st etc.

I have a regular date calendar joined to this table and Date from this table is used for slicer for users to pick date.

Coming to requirement, I need to create line graphs only with Month end data points. Based on date user selects on slicer, I gave few selection examples as well below.

I can bring in only Month end data records in dataset from database to simplify, that would look exaclty same as main table with only May 31st, June 28th, July 31st.. etc...

Date	DealName	TimeRange	Metric A	Metric B
June 1st	A	5Y/2Y	100	150
June 2nd	A	5Y	100	160
.	A	5Y/2Y	100	150
.	A	5Y	100	160
June 28th	A	5Y/2Y	100	150
July 1st	A	5Y	100	160
July 2nd	A	2Y	100	150
.	A	5Y	100	160
.	A	2Y	100	150
July 31st	A	5Y	100	160
Aug 1st	A	5Y/2Y	100	150
Aug 2nd	A	5Y	100	160
.	A	5Y	100	150
.	A	5Y	100	160
.	A	5Y/2Y	100	150
Aug 8th	A	5Y	100	160

selected value	Line graph should show data points for these Month ends
July 31st	July 31st and all Month ends before
August 5th	July 31st and all Month ends before
June 28th	June 28th and all Month ends before
June 10th	May 31st and all Month ends beofre

[mickey64 · Accepted solution]

Step 0: I use your data below.

Step 1: I add a 'Month' column and a 'Flag' column.

    Month = MONTH([Date])

    Flag = IF([Date]=CALCULATE(MAX(DATA[Date]),ALLEXCEPT('DATA','DATA'[Month])),1,0)

Step 2: I make a graph and set 'Flag' filter.

[andrezmar]

Hola manalla. Es importante que la columna de fecha, tenga formato de fecha.

Crear una columna nueva con la siguiente formula dax que te calcula el ultimo dia.

Último Día del Mes = ENDOFMONTH('Fechas'[Fecha])

Para la visualizacion, use la matriz. Al usar el campo Último Día del Mes, me totaliza por la jerarquia de fechas.

Cambiar la jerarquia de fechas por Ultimo dia del mes.

Si está satisfecho con esta respuesta, márquela como solución para que otros la encuentren.

[mickey64 · Accepted solution]

Step 0: I use your data below.

Step 1: I add a 'Month' column and a 'Flag' column.

    Month = MONTH([Date])

    Flag = IF([Date]=CALCULATE(MAX(DATA[Date]),ALLEXCEPT('DATA','DATA'[Month])),1,0)

Step 2: I make a graph and set 'Flag' filter.

[manalla]

Many thanks for the response @mickey64. I dont want August to be on graph as August month is not yet complete. Also when user selects any date in August, it should only display till July Monthends. 

With the formula, even the current last day (currently some day in August) is also getting flag as 1. 

Is there a way to control this?

Thanks

[v-linyulu-msft · Accepted solution]

Thanks for the reply from mickey64 , 3CloudThomas and andrezmar   , please allow me to provide another insight:

Hi, @manalla 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Create calculated column references:

LastWorkdayOfMonth = 
VAR LastDay = EOMONTH('Table'[Date], 0)
VAR LastWorkday = 
    SWITCH(
        WEEKDAY(LastDay, 2),
        6, LastDay - 1,  
        7, LastDay - 2,  
        LastDay          
    )
RETURN LastWorkday

3. Below are the measure I've created for your needs:

MEASURE A = 
VAR LastDay1 =
    EOMONTH ( MAX ( 'Table (2)'[selcet] ), 0 )
VAR LastDay2 =
    EOMONTH ( MAX ( 'Table (2)'[selcet] ), -1 )
VAR LastWorkday1 =
    SWITCH ( WEEKDAY ( LastDay1, 2 ), 6, LastDay1 - 1, 7, LastDay1 - 2, LastDay1 )
VAR LastWorkday2 =
    SWITCH ( WEEKDAY ( LastDay2, 2 ), 6, LastDay2 - 1, 7, LastDay2 - 2, LastDay2 )
RETURN
    IF (
        MAX ( 'Table (2)'[selcet] ) = LastWorkday1,
        CALCULATE (
            SUM ( 'Table'[Metric A] ),
            'Table'[LastWorkdayOfMonth] = MAX ( 'Table (2)'[selcet] )
        ),
        CALCULATE (
            SUM ( 'Table'[Metric A] ),
            'Table'[LastWorkdayOfMonth] = LastWorkday2
        )
    )

Measure B = 
VAR LastDay1 =
    EOMONTH ( MAX ( 'Table (2)'[selcet] ), 0 )
VAR LastDay2 =
    EOMONTH ( MAX ( 'Table (2)'[selcet] ), -1 )
VAR LastWorkday1 =
    SWITCH ( WEEKDAY ( LastDay1, 2 ), 6, LastDay1 - 1, 7, LastDay1 - 2, LastDay1 )
VAR LastWorkday2 =
    SWITCH ( WEEKDAY ( LastDay2, 2 ), 6, LastDay2 - 1, 7, LastDay2 - 2, LastDay2 )
RETURN
    IF (
        MAX ( 'Table (2)'[selcet] ) = LastWorkday1,
        CALCULATE (
            SUM ( 'Table'[Metric B] ),
            'Table'[LastWorkdayOfMonth] = MAX ( 'Table (2)'[selcet] )
        ),
        CALCULATE (
            SUM ( 'Table'[Metric B] ),
            'Table'[LastWorkdayOfMonth] = LastWorkday2
        )
    )

4.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[3CloudThomas]

Have an indicator in the Date dimension for Last Working Day of Month. Filter the page or visual by this column = 1(true)