I need to add a Year measure/field to dynamic date table

[corbett3442]

Hello!  I have a data model with multiple fact tables (all with the same start and end dates).  I have successfully created a date table that links to all of them.

When I use a slicer to select a business unit, the tables update as expected.  However , I need to be able to group the rows by relative year as well (instead of just having them show 1 month per row), so I need to create a relative year column/field.

So if I select a business unit with a start date of Feb-2021 I get the following:

2/21	Value 1
3/21	Value 2
4/21	Value 3
etc

All I need is another measure or field to add that gives me

2/21	year 1	value 1
3/21	year 1	value 2
...	...	...
2/22	year 2	value 13

I cannot do this in the actual date table because it includes all possible dates for all the business units, so I think I need it to be a measure?

I don't have much experience posting to this forum, so I apologize if I am not posting correctly!

Many thanks,

Jason

[mahoneypat]

Is there a lot of similarity between your tables for the different business units (i.e., same column names)?  If so, I would suggest you append those tables together in the query editor to simplify your model and your analysis/visualization.  I have seen people do that many times, so have to ask.

As for your post, you can add a calculated column (so you can use the values in category/legend for visuals) like this to get the relative year.  Replace with the table[column] name for your business unit table(s).  If you decide to append your tables, you can still do this but use ALLEXCEPT to get it relative for that business unit).  Let me know and I can send that version if needed.

Relative Year =
VAR yearmin =
    YEAR ( MIN ( Sales[SaleDate] ) )
VAR relyear =
    YEAR ( Sales[SaleDate] ) - yearmin + 1
RETURN
    "Year " & relyear

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[corbett3442]

Thanks for your help Pat!

Here's the formula I created.  I have gotten an aggregation error on the second variable b/c it did not have min/ max etc. so I added MIN()

Relative Yr =

VAR yearmin = YEAR ( MIN (MonthTable[Date].[Date] ) )

VAR relyear = YEAR ( MIN(MonthTable[Date].[Date]) ) - yearmin +1

RETURN relyear

It does not seem to work, and when I add it to the fact table visual it also includes all of the dates from the date table, not just the first date of the business unit I selected in the slicer.  Any way to have it filter to only include the dates between the star and end dates of the selected business unit?

Thanks

[mahoneypat]

The expression I provided was for a calculated column not a measure.  If you use it on your business unit table(s), it will create a column with Year 1, Year 2, etc. that you can use in visuals.  

If you want a measure, you can use this instead

Relative Yr =
VAR yearmin =
    YEAR (
        CALCULATE ( MIN ( MonthTable[Date] ), ALLSELECTED ( MonthTable[Date] ) )
    )
VAR relyear =
    YEAR ( MIN ( MonthTable[Date] ) ) - yearmin + 1
RETURN
    relyear

Regards,

Pat

[corbett3442]

Thanks Pat - The code for the measure worked, except for the fact that when I drop it into the table/visual it pulls in all months, including those prior to the first month of the business unit that I selected in the slicer.

My date table is based off of the PropertyAssets table:

Property ID	Analysis Begin	Analysis End
PropA	1/1/2021	12/31/2049
PropB	1/2015	12/31/2025

My date table code:

MonthTable =

var FullCalendar = ADDCOLUMNS(CALENDAR(min(PropertyAssets[Analysis Begin].[Date]),max(PropertyAssets[Analysis End].[Date])),"Month Number",MONTH([Date]),"Year",YEAR([Date]),"Year-Month",LEFT(FORMAT([Date],"yyyyMMdd"),6),"Month Name",FORMAT(MONTH([Date]),"MMM"),"Year-MonthName",YEAR([Date]) & " " & FORMAT(MONTH([Date]),"MMM"))

return

SUMMARIZE(FullCalendar,[Month Number],[Year],[Year-Month],[Year-MonthName])

So when I pull the fact tables in with the date table and slice by say Prop A, I get (as expected):

1/1/2021	Value1
2/2021	Value 2

but when I drop in the relative year measure it now shows

Date	Value	Relative Year
1/1/2015	null	1
2/1/2015	null	1
3/1/2015	null	1

because it is pulling in months from Prop B as well.

Any thoughts as to how to fix this part?

Many thanks for your time...

Jason

[corbett3442]

It seems as though I have not taken the right approach in setting up my data model, as I am unable to come up with a solution here.  Is it possible to get some consulting from an expert to potentially rework the model so it works with a single date table?

Thanks

[v-juanli-msft]

Hi @corbett3442 

Could you share a simple data example for me to work on?

Best Regards

Maggie