Changing the last day of the month line graph based on the range selected in the slicer

[Marico]

Hello Folks,

I have a date table and a main table (containing date and volume) in the dataset. The date table is connected to the main table. The following columns have been added to the date table to calculate the last day of the week, the last day of the month, and the last day of the quarter.

IsLastDayOfMonth = IF ( EOMONTH ( [Date], 0 ) = [Date]|| MAX(Sales[RandomDate]) = 'Date'[Date], TRUE (), FALSE () )

IsLastDayOfWeek = IF ( WEEKDAY ( [Date], 2 ) = 7, TRUE (), FALSE () )

Sum_Sales_LastDayOfMonth =

CALCULATE(

    SUM(Sales[Sales Amount]),

   'Date'[IsLastDayOfMonth] = TRUE()

)

Sum_Sales_LastDayOfWeek =

CALCULATE(

    SUM(Sales[Sales Amount]),

    'Date'[IsLastDayOfWeek] = TRUE()

)

As shown below, I have created a line chart for the last day of the month based on the field parameter.

Time Switch = {

    ("LastDayOfWeek", NAMEOF('Key Measures'[Sum_Sales_LastDayOfWeek]), 0),

    ("LastDayOfMonth", NAMEOF('Key Measures'[Sum_Sales_LastDayOfMonth]), 1)

}

Requirement: The graph should change based on the date range selected in the slicer as well. For e.g. If the date range selected

is 7/1/2017 to 9/12/2017 then the values shown the graph should be

1. 7/31

2. 8/91

3. 9/12 - end of selected date range and label it is last day of the selected range

Let me know if the sample file is needed.

Many thanks!

[Anonymous · Accepted solution]

Hi,  @Marico 

Thank you for your prompt response.

Based on your latest description, I have made adjustments to your Sum_Sales_LastDayOfMonth and Sum_Sales_LastDayOfWeek measures, keeping the other steps unchanged:

Sum_Sales_LastDayOfMonth = 
VAR mindate =
    CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR maxdate =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Sales Amount] ),
        FILTER (
            'Date',
            'Date'[IsLastDayOfMonth] = TRUE ()
                || 'Date'[Date] = mindate
                || 'Date'[Date] = maxdate
        )
    )

Sum_Sales_LastDayOfWeek = 
VAR mindate1 =
    CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR maxdate1 =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Sales Amount] ),
        FILTER (
            'Date',
            'Date'[IsLastDayOfWeek] = TRUE ()
                || 'Date'[Date] = mindate1
                || 'Date'[Date] = maxdate1
        )
    )

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.

[Anonymous · Accepted solution]

Hi, @Marico 

Thank you for your prompt response.

1.You can create the following three measures:

start of selected range = 
    CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )

end of the selected range. = 
    CALCULATE ( MAX( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )

current date = MAX('Date'[Date])

2.Then, apply them to the tooltips section:

3.Below is the final result:

For more details, please refer to:

Create report tooltip pages in Power BI - Power BI | Microsoft Learn

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.

[Marico]

@Greg_Deckler @amitchandak @lbendlin @Ritaf1983 @rajendraongole1 any ideas on solution for the problem stated in description?

[lbendlin]

Group by month, find the last date with data in each month, plot that.

[Anonymous]

Thanks for the reply from DataNinja777 , please allow me to provide another insight:

Hi, @Marico 

I apologise, but in my sample data, the DAX you provided yields the desired results.
Here's my final result, which I hope meets your requirements：
 

Therefore, please try the following troubleshooting steps:

1.Firstly, ensure your relationships are correct.

2.Secondly, check the parameter settings of your visualisation object.Since your conditional statement is based on the date table, I recommend using the date from the date table as the x-axis parameter of the visualisation object.

3.Next, be aware that there may be instances where the results are not refreshed. This is due to the priority of your slicer selection. Simply reselect the slicer options.

4.Finally, continue to maintain your visualisation object settings.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.We recommend uploading it to GitHub and sharing the link with us.When uploading a file, please be careful to delete sensitive information.

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.

[Marico]

Hi team, 

Thank you for looking into this.

I took the below screenshot from the file (lastday) you had attached in the previous comment.

As per the requirement, along with the last day of the month between the selected range in the slicer, I need to show the start date (8/21/22 - below example) and end date (4/6/2023 - below example) as well in the line chart (x-axis).

Let me know if I need to elaborate the requirement more.

[Anonymous · Accepted solution]

Hi,  @Marico 

Thank you for your prompt response.

Based on your latest description, I have made adjustments to your Sum_Sales_LastDayOfMonth and Sum_Sales_LastDayOfWeek measures, keeping the other steps unchanged:

Sum_Sales_LastDayOfMonth = 
VAR mindate =
    CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR maxdate =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Sales Amount] ),
        FILTER (
            'Date',
            'Date'[IsLastDayOfMonth] = TRUE ()
                || 'Date'[Date] = mindate
                || 'Date'[Date] = maxdate
        )
    )

Sum_Sales_LastDayOfWeek = 
VAR mindate1 =
    CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR maxdate1 =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Sales Amount] ),
        FILTER (
            'Date',
            'Date'[IsLastDayOfWeek] = TRUE ()
                || 'Date'[Date] = mindate1
                || 'Date'[Date] = maxdate1
        )
    )

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.

[Marico]

EXCELLENT! This exactly what I needed.

I just want to add a tooltip also show the start of selected range, end of the months and end of the selected range. Could you please help me with this as well?

[Anonymous · Accepted solution]

Hi, @Marico 

Thank you for your prompt response.

1.You can create the following three measures:

start of selected range = 
    CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )

end of the selected range. = 
    CALCULATE ( MAX( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )

current date = MAX('Date'[Date])

2.Then, apply them to the tooltips section:

3.Below is the final result:

For more details, please refer to:

Create report tooltip pages in Power BI - Power BI | Microsoft Learn

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.

[Marico]

Thank you for sharing this but I would like to create a custom tooltip instead of all the measures in the tooltips section.

Also, if the real data sales table doesn't have all the dates of a calendar year, can we use the below dax for the calendar table?

Date = CALENDAR(MIN(Sales_Table [Date]),TODAY())

[Anonymous]

Hi, @Marico 

Thank you for your prompt response.

Firstly, regarding your custom tooltips, I am not entirely clear on your requirements. According to the official definition, the solution I provided is considered custom tooltips:

For more details, please refer to:

Customizing tooltips in Power BI Desktop - Power BI | Microsoft Learn
 

Secondly, regarding whether we can use the DAX you provided on the Calendar table, my answer is yes. You just need to select the date and make the necessary modifications.

You may need to ensure that the relationship between the two tables is maintained:

Please find the attached pbix relevant to the case.

 
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

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.
 

[Marico]

Regarding custom tooltip, my requirement is forthese start date of selected date range, end of selected date range and current date, create a new tooltip page and link to it the visual from General -> tooltip section. Could you help me create that tooltip page please?

[Anonymous · Accepted solution]

Hi, @Marico 

Thank you for your prompt correction.

Since the functionality you desire inherently includes a corresponding date filter for the data, but we also need to retain the slicer's filtering on the visual object, we will abandon the virtual table solution. Therefore, I suggest creating a new date table to serve as the date slicer:

1.Firstly, you need to create a calculation table and use it as a filter:

Date1 = CALENDAR(MIN('Sales'[Date]),TODAY())

Be careful not to create table relationships:

2.Secondly, modify the Sum_Sales_LastDayOfMonth and Sum_Sales_LastDayOfWeek measures:

Sum_Sales_LastDayOfMonth = 
VAR mindate =
    CALCULATE ( MIN ( 'Date'[Date] ), FILTER(ALLSELECTED ( 'Date' ),'Date'[Date] IN VALUES(Date1[Date])))
VAR maxdate =
    CALCULATE ( MAX ( 'Date'[Date] ), FILTER(ALLSELECTED ( 'Date' ),'Date'[Date] IN VALUES(Date1[Date]) ))
RETURN
    CALCULATE (
        SUM ( 'Sales'[Sales Amount] ),
        FILTER (
            'Date',
            'Date'[IsLastDayOfMonth] = TRUE ()
                || 'Date'[Date] = mindate
                || 'Date'[Date] = maxdate
        )
    )

Sum_Sales_LastDayOfWeek = 
VAR mindate1 =
    CALCULATE ( MIN ( 'Date'[Date] ), FILTER(ALLSELECTED ( 'Date' ),'Date'[Date] IN VALUES(Date1[Date])) )
VAR maxdate1 =
    CALCULATE ( MAX ( 'Date'[Date] ), FILTER(ALLSELECTED ( 'Date' ),'Date'[Date] IN VALUES(Date1[Date])) )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Sales Amount] ),
        FILTER (
            'Date',
            'Date'[IsLastDayOfWeek] = TRUE ()
                || 'Date'[Date] = mindate1
                || 'Date'[Date] = maxdate1
        )
    )

3.Next, create the following measure to serve as the date filter:

date filter = 
IF( MAX('Date'[Date]) in VALUES(Date1[Date]),1,0)

4.Then, modify the end of the selected range and start of selected range measures I created earlier:

end of the selected range. = 
CALCULATE ( MAX( 'Date1'[Date]), ALLSELECTED ( 'Date1' ) )

start of selected range = 
    CALCULATE ( MIN ( 'Date1'[Date]), ALLSELECTED ( 'Date1' ) )

5.Create a new page, adjust the page size, and set it to be used as tooltips:

6.Add the following three values to the page and adjust the red box area to fit the page size:

7.Return to the visualisation and modify it to use the tooltips page, and apply the date filter measure to the visualisation:

8.Below is the final result:

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.

[Marico]

Perfect! This works well. Thanks a lot for solving this case so quickly. @Anonymous 

[DataNinja777]

Hi @Marico ,

To ensure the graph dynamically adjusts to the selected date range and includes the last day labeled as "Last Day of Selected Range," adjust the DAX formulas using VAR for efficiency. In the Date table, add a calculated column to identify the last day of the selected range:

IsLastDayOfSelectedRange = IF('Date'[Date] = MAX('Date'[Date]), TRUE(), FALSE())

Create a measure for the last day's sales amount:

Sum_Sales_LastDayOfSelectedRange = 
CALCULATE(SUM(Sales[Sales Amount]), 'Date'[IsLastDayOfSelectedRange] = TRUE())

Combine the logic for last day of the month, week, and selected range into one dynamic measure:

Dynamic_Sum_Sales =
VAR TotalSales = SUM(Sales[Sales Amount])
RETURN
    CALCULATE(
        TotalSales,
        SWITCH(
            TRUE(),
            'Date'[IsLastDayOfMonth], TRUE(),
            'Date'[IsLastDayOfWeek], TRUE(),
            'Date'[IsLastDayOfSelectedRange], TRUE(),
            FALSE()
        )
    )

Update the field parameter to include all relevant measures:

Time Switch = {
    ("LastDayOfWeek", NAMEOF('Key Measures'[Sum_Sales_LastDayOfWeek]), 0),
    ("LastDayOfMonth", NAMEOF('Key Measures'[Sum_Sales_LastDayOfMonth]), 1),
    ("LastDayOfSelectedRange", NAMEOF('Key Measures'[Sum_Sales_LastDayOfSelectedRange]), 2)
}

Use Dynamic_Sum_Sales in your line chart, with the Date column on the X-axis, and connect the slicer to the Date table for dynamic filtering. For labeling the last day of the selected range, create a measure:

LastDayLabel = 
IF('Date'[IsLastDayOfSelectedRange], "Last Day of Selected Range", FORMAT('Date'[Date], "MM/dd"))

This label can be displayed as a tooltip or in the visual for clear identification. 

Best regards,

[Marico]

Not sure what's wrong here but for the mentioned date range in the slicer 1/7 to 11/11,

e.g. for last day of month I should see only points 31/7, 31/8,30/9,31/10 and11/11 in the graph, similar for last of the quarter it should be 30/9,11/11

I am using Adventure Works DW 2020 database for this sample.