topic Re: Filter first 12 month data from each Unique Data in DAX Commands and Tips

Filter first 12 month data from each Unique Data

Anonymous — Thu, 09 Feb 2023 14:20:23 GMT

Hi All

I have huge data set available and would like to filter for FIRST 12 month of each Unique data. Date for each Unique Data may start at different dates but i need to plot first 12 month of each Unique Data.

I have following Columns:

"Unique Data" , "Date", "Value"

Can someone help me with this?

Thanks

Regards

Timur

Re: Filter first 12 month data from each Unique Data

johnt75 — Thu, 09 Feb 2023 15:23:09 GMT

You could create a table to store the dates for each item, e.g.

Start and end dates = GENERATE ( DISTINCT ( 'Table'[Unique ID] ), VAR StartDate = CALCULATE ( MIN ( 'Table'[Date] ) ) VAR EndDate = EOMONTH ( StartDate, 12 ) RETURN ROW ( "Start Date", StartDate, "End Date", EndDate ) )

and then link this to your fact table. If you already have a dimension table you could just add the start and end date columns to that.

You can then create a measure like

First 12 months data = SUMX ( 'Dim table', VAR StartDate = 'Start and end dates'[Start Date] VAR EndDate = 'Start and end dates'[End date] RETURN CALCULATE ( SUM ( 'Table'[value] ), KEEPFILTERS ( DATESBETWEEN ( 'Date'[Date], StartDate, EndDate ) ) ) )

Re: Filter first 12 month data from each Unique Data

Anonymous — Thu, 09 Feb 2023 23:11:18 GMT

Hi johnt75

Thanks for your prompt reply. But i am still having some issues with above code:

It says cannot convert to a scalar value. Can you please let me know how to fix it?

Many thanks

Re: Filter first 12 month data from each Unique Data

johnt75 — Fri, 10 Feb 2023 09:38:02 GMT

Are you trying to create that as a measure? Because it needs to be a table not a measure.

Re: Filter first 12 month data from each Unique Data

Anonymous — Sat, 11 Feb 2023 00:55:33 GMT

Dear johnt75

I have created "New Column" using this code and still saying cannot convert scalar value

😞

Re: Filter first 12 month data from each Unique Data

FreemanZ — Sat, 11 Feb 2023 02:07:09 GMT

hi Anonymous

the code is to create a new table, not a new column

Re: Filter first 12 month data from each Unique Data

Anonymous — Sat, 11 Feb 2023 02:23:25 GMT

Dear FreemanZ

Thank you for your response. I have managed successfuly create a table but now when trying to link to main table via "new measures", there is following error message:

is there any steps i am missing?

Re: Filter first 12 month data from each Unique Data

FreemanZ — Sat, 11 Feb 2023 02:53:25 GMT

hi Anonymous

you have too much unmentioned tables, describe your case clearly.