Filter first 12 month data from each Unique Data

[Anonymous]

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

[johnt75]

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 ) )
        )
)

[Anonymous]

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

[johnt75]

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

[Anonymous]

Dear @johnt75 

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

😞

[FreemanZ]

hi @Anonymous 

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

 

[Anonymous]

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?

[FreemanZ]

hi @Anonymous 

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