Dynamic x-axis for Month/Year in a chart based upon single slicer selection

[Anonymous_007]

Hello everyone, 

Greetings!

I'm having a problem creating a chart which should be having dynamic x-axis with respect to the values in the slicer selected. I'm having a date table in my dataset, also, I have tried the same to be done using disconnected table concept but no luck so far. My purpose is to get values like "April 2024", "May 2024", "June 2024" in the chart whenever value in the slicer selected as "June 2024", similarly values like "April 2023", "May 2023", "June 2023", "July 2023", "August 2023" whenever I select value as "August 2023" in the slicer. Basically, i want YTD values to be shown in the chart on a single select slicer value. 

I have a column as "Actual date" in my base data set having data format as "mmmm yyyy" and no date, also, i have a connected table as "Date  table" generated using "CALENDARAUTO()" function and a disconnected table as well using the same function. I have tried wiriting a dax as well which is as provided below and got the functionality I desired which is I am getting year/month range on single slicer select value but values of my measure are not populating as correct.

Requesting you all to please help, the dax I am using is as provided below and here "Energy Input'[Actual_Date]", is my date column in the main dataset table named as "Energy Input", "Presentation Calendar'[Date]" is the date column from the disconnected table named as "Presentation Calendar" and "[Bill_EFF_YTD_FINAL]" is my measure which i want to show on a chart:

Bill Eff | YTD bars =
VAR SelectedDate = SELECTEDVALUE('Energy Input'[Actual_Date])

VAR StartDate = DATE(YEAR(SelectedDate) - IF(MONTH(SelectedDate) >= 4, 0, 1), 4, 1)

VAR EndDate = SelectedDate

VAR Result = IF(HASONEVALUE('Presentation Calendar'[Date]) &&
MAX('Presentation Calendar'[Date]) <= EndDate &&
MIN('Presentation Calendar'[Date]) >= StartDate,
CALCULATE(
[Bill_EFF_YTD_FINAL],
FILTER(
ALL('Energy Input'),
'Energy Input'[Actual_Date] = VALUES('Presentation Calendar'[Date]))),

BLANK())
RETURN Result

[Anonymous · Accepted solution]

Hi,@Anonymous_007 I am glad to help you.

According to your description, you want to implement the slicer selection to complete the filtering of successive month data according to the written dax, if no month does not meet the requirements, return empty, and always show the data from April.
For example:
If "June 2024" is selected, the start date will be "April 1, 2024."
If "March 2023" is selected, the start date will be "April 1, 2022".

Your code does not seem to have logical problems, and I will create simulation data according to your code to check whether there is a calculation error in your code
I made appropriate modifications on the basis of the original measure and realized your requirements. When the time period of August 2022 is selected, test returns the corresponding calculation data from April to August of that year (because you did not provide the code of measure [Bill_EFF_YTD_FINAL] here, So I use the following custom measure for the simulation, which in fact does not affect the overall calculation beam logic)

Bill_EFF_YTD_FINAL = MAX('Energy Input'[Sales])

When the selected month is less than April, the data from April of the previous year through the selected period is returned: April 2022-March 2023

Below is the detailed code and test data

test = 
VAR SelectedDate = SELECTEDVALUE('Presentation Calendar'[Date])
//Get the calendar table (date column on the slicer instead of the original table's date column Actual_Date)
VAR StartDate = DATE(YEAR(SelectedDate) - IF(MONTH(SelectedDate) >= 4, 0, 1), 4, 1)
VAR EndDate = SelectedDate
VAR Result = 
IF(HASONEVALUE('Presentation Calendar'[Date]) &&
MAX('Presentation Calendar'[Date]) <= EndDate &&
MIN('Presentation Calendar'[Date]) >= StartDate,
    CALCULATE(
        [Bill_EFF_YTD_FINAL],
        FILTER(
            'Energy Input',
            'Energy Input'[Actual_Date] >= StartDate &&
            'Energy Input'[Actual_Date] <= EndDate
        )
    ),
    BLANK()
)
RETURN Result

Bill_EFF_YTD_FINAL = MAX('Energy Input'[Sales])
// Get the data for each month through the max function, simulating the measure not given in your code

If you can provide pbix files that do not contain sensitive data and share them to the forum via OneDrive or other means, it will be very helpful to solve your problem.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pdlv]

Can u help me please, Export data problem