Get last 6 months in DAX with limited data

[Pillz]

My current example dataset I'm working is below:

Event Name	Date	Type
Event 1	13-Apr-20	In Person
Event 2	5-May-20	Online
Event 3	21-Jun-20	Online
Event 4	5-Feb-20	In Person
Event 5	24-Feb-20	Online

I'm trying to use DAX to create a table that shows # of events across last 6 months similar to below. However without data on July/August, I'm not sure how to create columns to those 2 months. Is there any way I can create the table using these constraints?

	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20
In Person	1	0	1	0	0	0	0
Online	1	0	0	1	1	0	0

[Icey · Accepted solution]

Hi @Pillz ,

If you want to display the last 6 months in the table. You can try to create a calendar table. There are two ways to achieve its functionality: implemented with a filter or implemented in a table.

1. implemented in a filter:

a. You can create a calendar table like below and then sort [MonthName-Year] column by [Month-Year] column.

Calendar = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2020, 12, 31 ) ),
    "Month", MONTH ( [Date] ),
    "Year", YEAR ( [Date] )
)

MonthName = FORMAT ( 'Calendar'[Date], "mmm" )

MonthName-Year = 'Calendar'[MonthName] & "-" & 'Calendar'[Year]

Month-Year = 'Calendar'[Month] & "-" & 'Calendar'[Year]

b. Create relationships between the two tables.

c. Create a measure.

count = COUNTROWS('Table')+0

d. Create a Matrix visual and set a filter on the visual.

2. implemented in a table:

You can just create a dynamic calendar table containing only the dates of the previous 6 months based on today's date.

Last 6 months = 
ADDCOLUMNS (
    CALENDAR ( EOMONTH ( TODAY (), -7 ) + 1, EOMONTH ( TODAY (), 0 ) ),
    "Month", MONTH ( [Date] ),
    "Year", YEAR ( [Date] )
)

MonthName = FORMAT('Last 6 months'[Date],"mmm")

MonthName-Year = 'Last 6 months'[MonthName]&"-"&'Last 6 months'[Year]

Month-Year = 'Last 6 months'[Month]&"-"&'Last 6 months'[Year]

And then, follow the steps of the previous method. However, there is no need to filter on date.

For more details, please check the attached .pbix file.

Best Regards,

Icey

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

[parry2k]

@Pillz you should create a measure like this

Count = COUNTROWS ( Table ) + 0

and then use this measure in the matrix.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡