Monthly view via Measure or is there a simpler/better column view?

[Anonymous]

Hi all,

If I want to display month values as columns in a table do I have a better way than the one I chose?

I have created **twelve** measures filtering on the month:

"EK in April = CALCULATE ([purchase value], FILTER ( EK_DATA_manual, EK_DATA_manual[month] = 4 ))"

In the long run, however, it becomes quite a lot to have additional tables that reference other data tables. Another way might be to "unpivot" the table.

How do we solve something like that?

Regards,

Nawaz

[Ashish_Mathur]

Hi,

How is your raw data structured.  Show that and your expected result.

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

If your data is divided into multiple years, I suggest that you can display the year and month together for group display, because if you only display the month, when calculating, a certain month of multiple years will be calculated together , instead of calculating a certain year and month separately.

1. Use the CALENDAR function to create a calendar table with dates from DATE(2020,1,1) to DATE(2021,7,27)

2. Create a calculated column

Year_Month =
YEAR('Table'[Date])&"-"&MONTH('Table'[Date])

3. Result.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[DataZoe]

@Anonymous have you tried using the Matrix visual?  You can then put [Purchase Value] in Values, then [Month] in the Columns, just like a pivot table.