Yearly and half Yearly Comparison

[Anonymous]

Hi All,

According to my requirement I have a data model , which has a Year columns .

Some Entities are recorded for 17 End Year,

Some Entities are recorded for 17 Mid Year

Some Entities are recorded for 18 End Year,

Some Entities are recorded for 18 Mid Year,

Some Entities are recorded for 19 End Year,

Some Entities are recorded for 19 Mid Year

The Year Column contains values as :

17 End Year

17 Mid Year

18 End Year and so on.

The column entities have the Entities Names  .

I want to display records as selected by user via a filter for All the Mid years or End Years,

I am currently using matrix , but unable to display Same Year different Periods.

The user may all the combinations possible but two at a time.

Any help would be appriciated.

Thanks

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

One sample for your reference. Please check the following steps as below.

1. Create a calculated column in your fact table.

Column = MID(Table1[YEAR],4,3)

2. Create a new calculated table and create relationship between it and the fact table.

Table 2 = VALUES(Table1[Column])

3. Then we can create a measure as below.

Measure = CALCULATE(SUM(Table1[Entities]))

Please find the pbix as attached.

Regards,

Frank

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

One sample for your reference. Please check the following steps as below.

1. Create a calculated column in your fact table.

Column = MID(Table1[YEAR],4,3)

2. Create a new calculated table and create relationship between it and the fact table.

Table 2 = VALUES(Table1[Column])

3. Then we can create a measure as below.

Measure = CALCULATE(SUM(Table1[Entities]))

Please find the pbix as attached.

Regards,

Frank

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank

[Jorgast]

I would create a column that identifies Mid Year (Year Number) or End Year (Year Number) and use that column as your slicer.