Reply
manalla
Helper V
Helper V
Partially syndicated - Outbound

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

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...

 

DateDealNameTimeRangeMetric AMetric B
June 1stA5Y/2Y100150
June 2ndA5Y100160
.A5Y/2Y100150
.A5Y100160
June 28thA5Y/2Y100150
July 1stA5Y100160
July 2ndA2Y100150
.A5Y100160
.A2Y100150
July 31stA5Y100160
Aug 1stA5Y/2Y100150
Aug 2ndA5Y100160
.A5Y100150
.A5Y100160
.A5Y/2Y100150
Aug 8thA5Y100160

 

selected valueLine graph should show data points for these Month ends
July 31stJuly 31st and all Month ends before
August 5thJuly 31st and all Month ends before
June 28thJune 28th and all Month ends before
June 10thMay 31st and all Month ends beofre
2 ACCEPTED SOLUTIONS
mickey64
Super User
Super User

Syndicated - Outbound

Step 0: I use your data below.

mickey64_0-1723314758738.png

 

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)

mickey64_2-1723314944547.png

 

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

mickey64_1-1723314926192.png

 

View solution in original post

Syndicated - Outbound

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:

vlinyulumsft_0-1723539123887.png

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.

vlinyulumsft_1-1723539206562.png

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.

View solution in original post

5 REPLIES 5
andrezmar
Resolver I
Resolver I

Syndicated - Outbound

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])

 

andrezmar_0-1723314822233.png

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

andrezmar_1-1723315000962.png

Cambiar la jerarquia de fechas por Ultimo dia del mes.

andrezmar_2-1723315175949.png

 

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

 

mickey64
Super User
Super User

Syndicated - Outbound

Step 0: I use your data below.

mickey64_0-1723314758738.png

 

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)

mickey64_2-1723314944547.png

 

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

mickey64_1-1723314926192.png

 

Syndicated - Outbound

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

Syndicated - Outbound

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:

vlinyulumsft_0-1723539123887.png

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.

vlinyulumsft_1-1723539206562.png

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
Super User
Super User

Syndicated - Outbound

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

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)