- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...
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 |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Have an indicator in the Date dimension for Last Working Day of Month. Filter the page or visual by this column = 1(true)
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
03-20-2024 01:56 AM | |||
05-21-2024 01:01 AM | |||
07-17-2024 09:45 PM | |||
12-05-2022 02:56 AM | |||
06-17-2024 07:06 PM |
User | Count |
---|---|
123 | |
80 | |
59 | |
58 | |
44 |
User | Count |
---|---|
182 | |
119 | |
82 | |
68 | |
53 |