X rows of year and months in columns (many types of data per year and month)

[piotrkopti]

Hello,

customer asked to provide with PowerBI report based on Excel 🙂 to show data aggregated by year, month and type. Important in this scenario is fact, that Types are not in raw data. I have some calculations based on dates (Type A: If date A > B then true else false, Type B: if date C > D then true else false ect.).

For example:

Year	Type	Month1	Month2	Month3	Month4
2021	A	21	54	7	34
2021	B	4	6	8	9
2021	C	5	456	546	4
2020	A	546	45	45	546
2020	B	65	546	6	546
2019	B	645	456	56	4
2019	C	456	6	54	4

I struggle to achieve this, using table or matrix, is there any way to do this exactly like that?

So far I have something like this in matrix:

but customer is asking for something more like he showed in excel table (arguing that his table gives more clarity about year to month changes).

[piotrkopti]

Any idea? I'm I able to do this?

[v-easonf-msft]

Hi,  @piotrkopti 

Not clear what do you want?

If you just want the following matrix structure, please try to drag the year field to matrix ‘rows’ and the month field to matrix ‘cloumns’.

If it involves changes to the table structure, you may need to try some functions in the query editor such as Pivot ,Unpivot,Group by...

Please share your original data and your expected results directly in excel for further research.

Best Regards,
Community Support Team _ Eason

[piotrkopti]

My raw data contains 6 dates for each row and some other data like customer name etc. Those "types" that I mentioned are not in the raw data. Those types are results of date fields comparassions.

I need to compare dates with each other to determine some dependencies.

For example:
if date A > B then Type A is 1, otherwise Type A is 0.

if date C > D then Type B is 1, otherwise Type B is 0.

if date E > F then Type C is 1, otherwise Type C is 0.

My customer wish to see this comparison as follows:

[v-easonf-msft · Accepted solution]

Hi, @piotrkopti 

If you want to generate such a table, your column “Type” must be an actual column , which can be a column in a calculated table or a calculated column.

If it is a measure, I am afraid that it cannot be implemented at present, because the measure can only be applied to the value of the matrix.
If possible, try to convert it to a calculated column.

Best Regards,
Community Support Team _ Eason