YTD custom logic

[NimaiAhluwalia]

How would you write a dax code in power bi when you have a date table and it is connected to you sales fact table with the date column and you want to create a bar chart which shows the data of YTD sales as of 08/01/2025 and when we move back on the graph it will shows me the sales of YTD till 08/12/2024 and if we move more backwards than 08/11/2024, each data point in the graph would be the YTD till the current date which is 8th Jan but the month and the year would be different. 

and if date is 09/01/2025 than all the past pointers will be 09/12/2024 , 09/11/2024, when i scroll my mouse on the graph.
and if date is 10/01/2025 than all the past pointers will be 10/12/2024 , 10/11/2024, when i scroll my mouse on the graph.

and if date is 22/01/2025 than all the past pointers will be 22/12/2024 , 22/11/2024, when i scroll my mouse on the graph.

exmaple 1

example 2

example 3

[v-heq-msft · Accepted solution]

Hi @NimaiAhluwalia ,
According to your description, you can use tooltip to do this, here are the steps to implement it.
First, create a calculated column to calculate the cumulative value.

Cumulative Sales = 
VAR _current = 'Fact Table'[Date]
RETURN
CALCULATE(
    SUM('Fact Table'[Sales Amount]),
    FILTER(
        'Fact Table',
        'Fact Table'[Date] <= _current
    )
)

Then, create a filter table that is not related to any other table.

Slicer Table = VALUES('Fact Table'[Date])

Next, create measures

DateKey = 
VAR _selectedDate = MAX('Slicer Table'[Date])
VAR _day = DAY(_selectedDate)
VAR _currentYear = Year(SELECTEDVALUE('Fact Table'[Date]))
VAR _currentMonth = MONTH(SELECTEDVALUE('Fact Table'[Date]))
VAR _sameDateLastMonth = DATE(_currentYear,_currentMonth,_day)
RETURN
_sameDateLastMonth

IsBeforeSelectedDate = 
IF(
    SELECTEDVALUE('Fact Table'[Date]) <= MAX('Slicer Table'[Date]),
    1,
    0
)IsBeforeSelectedDate = 
IF(
    SELECTEDVALUE('Fact Table'[Date]) <= MAX('Slicer Table'[Date]),
    1,
    0
)

Then you need to create a tooltip page

On the final page, you need to configure filters for the visualization

and enable tooltip

Final output

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

Best regards,
Albert He

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