Get end of the selected month in a column

[Anonymous]

Edited

*******************

Thanks to @Greg_Deckler @amitchandak ,

I've made a Aged tickets category as a different table and successfully assigned the value in it.

aged max date = 
    CALCULATE(
        SELECTEDVALUE('Aged Tickets'[Days], BLANK()), 
        FILTER(ALL('Aged Tickets'), 
            [Aged Tickets Days] >= 'Aged Tickets'[Min] && 
            [Aged Tickets Days] <= 'Aged Tickets'[Max])
    )

 

Now I'm trying to count the rows but it looks like a new measure or column doesn't read the value in the [Aged Tickets Days] at all. 

I attach a sample file this time, if you could look at this would be much appreciated!

Data link 

**************

Hi,

I've been trying to get a time duration based on a user month selection and I've managed to do with a measure but I realised that the measure only returns a single value, not very suitable to organize aged tickets matrix ("< 30 days", "30 - 60 days", "60+ days"...).

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Aged-tickets-calculation-using-measure/m-p/13...

Now I came back to creating a column, want to know how to get a variable changing to the end of the selected month.

For example, if you look at the "aged test" column I want to get the _max value to be June 30th, 2020 for all rows so it will calculate how many days it has been since the ticket created IF the ticket is not closed yet. (Only for row with "Closed Date" empty)

"Aged Tickets Days" shows the correct result but since it is a measure, I found it difficult to utilize.

I've tried DAX like,

var _max = CALCULATE(ENDOFMONTH(DateKey[Date]), ALLEXCEPT(DateKey, DateKey[Date]))
var _max = CALCULATE(ENDOFMONTH(DateKey[Date]), FILTER(ALL(DateKey[Date]), MONTH(DateKey[Date]) = SELECTEDVALUE(DateKey[Date])))
var _max = CALCULATE(MAXX(DateKey, DateKey[Date]))
and more...

 but none of them worked as I expected.

Thank you so much for your time, any help would be appreciated 🙂 

[amitchandak]

@Anonymous , To me it seems like Case of Dynamic Segmentation

You have to create an independent bucket table and join that with your age in a measure.

refer if these can help

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://radacad.com/dynamic-banding-or-grouping-in-power-bi-using-dax-measures-choose-the-size-of-bins
https://www.credera.com/blog/technology-solutions/creating-aging-report-using-a-user-selected-date-in-power-bi/

https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[Greg_Deckler]

@Anonymous - A calculated column is only calculated at the time of data load/refresh. Therefore, it cannot be dynamic based upon a slicer selection. I believe what you want is a disconnected table with your buckets. You can put that into your visual and have your measure take it into account. For example:

Measure =
  VAR __Table = ADDCOLUMNS('Table',"Measure",[Measure])
RETURN
  SWITCH('DisconnectedTable'[Column],
    "< 30 days",COUNTROWS(FILTER__Table,[Measure]<30)),
    "30 - 60 days",COUNTROWS(FILTER(__Table,[Measure]>=30 && [Measure]<=60)),
    COUNTROWS(FILTER__Table,[Measure]>60))
  )

In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563