Compare Selected Month to Month on Date

[Syndicate_Admin]

Hello, I have a graph in which the x-axis is the months of the year, I also have a column with a solution date, what I need is that if the solution date is less than or equal to the selected month, count the values related to that month

Only the fourth record would not correspond to the rule I need. Do you have any ideas on how to do this?

I appreciate your help.

[v-nuoc-msft]

Hi @Syndicate_Admin 

For your question, here is the method I provided:

Here's some dummy data

"Table"

"Date"

date = CALENDAR("1/1/2024", "12/31/2024")

Create a slicer visual object based on a date table for selecting a month.

Create a measure, query the selected month.

_month = SELECTEDVALUE('date'[Date].[MonthNo])

Create a measure. When the resolution date is less than or equal to the selected month, it is marked as 1, otherwise it is 0.

mark = 
IF(
    MONTH(SELECTEDVALUE('Table'[fecha_EstSol])) <= [_month],
    1, 
    0
)

You can put that measure into Filters for filtering and not show it in the visual object.

Here is the result.

Regards,

Nono Chen

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

[Syndicate_Admin]

Hi, I've tried your solution and have some issues.

At the time of selecting month 4, "valid" as of 09/05/2024 as true, it will have something to do with the fact that the date table is linked to a column of dates in my table where column Fecha_EstSol is, the problem is that the measurement I have related to the table is calculated like this:

Active1 =

were _min_date = minx(all('Date'),'Date'[Date])

were _Expression=if(ISFILTERED('Date'[Year]),maxx('Date',ENDOFMONTH(DATEADD('Date'[Date],-1,MONTH))),maxx('Date',DATEADD('Date'[Date],-1,YEAR)))

Return

CALCULATE(COUNTx(FILTER('demosgsi scanningvulnerabilitiesdetail','demosgsi scanningvulnerabilitiesdetail'[fecha_IniEsc]<=_Expression && 'demosgsi scanningvulnerabilitiesdetail'[fecha_IniEsc]>=_min_date && (ISBLANK('demosgsi scanningvulnerabilitiesdetail'[fecha_FinAten]) || 'demosgsi scanningvulnerabilitiesdetail'[fecha_FinAten]>_Expression)),('demosgsi scanningvulnerabilitiesdetail'[id])),CROSSFILTER('demosgsi scanningvulnerabilitiesdetail'[fecha_IniEsc],'Date'[Date],None))

Here's what it looks like by selecting a month

Here's what unselected data looks like

What I need is what you show in your table next to the Start value, but it looks modified with the selection.

[Ritaf1983 · Accepted solution]

Hi @Syndicate_Admin 
If the calculation is in the same year then you can use TOTALYTD dax function.
For example, if I want to count the orders, whose order date is less than or equal to the selected month it will look like this:

The pbix is attached

More information about the function here :

https://www.youtube.com/watch?v=BNZSoTYPYQs

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