DAX to build calendar based on views/usage metrics from PB

[Mous007]

Hi everyone,

So i am trying to start a new report to track the usage of dashboard/report (e.g Active users, number of views, views per region ....)

Before i begin, i noticed that the report are only for the last 3 months period and i have downloaded the usage report files since December 2019.

I have the files all together in one folder and I know how to load them from a folder. the problem is that some dates/view are duplicate as the file share the same period. 

My quest has two distinct parts and i would welcome any possible help.

1) First, i want to use dax to generate a calendar table for my report based on the earliest date available (11/15/2019 in this case) but i want also to know if i can use dax to dynamically change the latest date on my model (as i will be adding more and more report files) maybe using Max function or anything similar ?

2) If i manage to create my dates model based on the previous point, how can i be sure my dax measures or calculated columns only count each date and views by each user only once so that i will have no duplicates in my report ?

the extract from the usage report as you may already know looks like the following:

User Principal NameViewsDatePlatformDistributionMethodReportPage

User 1	3	2019-11-15	Web	Content Pack: M	View 1
User 1	3	2019-11-15	Web	Workspace	View 2
User 1	2	2019-11-15	Web	Content Pack: M	View 2
User 1	1	2019-11-15	Web	Workspace	View 1
User 2	7	2019-11-15	Web	Content Pack: M	View 1
User 3	6	2019-11-15	Web	Content Pack: M	View 2
User 2	6	2019-11-15	Web	Content Pack: M	View 2
User 4	3	2019-11-15	Web	Content Pack: M	View 1

I might not be clear enough but please feel free to ask any questions as i have never used custom calendars on powerbi.

Thanks in advance

[Mariusz]

Hi @Mous007 

The below article explains how to create a calendar

https://powerbi.tips/2017/11/creating-a-dax-calendar/

with few tweaks, you can adjust it to start and end  at a certain date,
MIN( table[dateColumn] ) and  MAX( table[DateColumn] )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Mous007]

Hi @Mariusz , i have read the article and used ur trick and this is what i have so far:

DatesTable =

VAR MinYear = YEAR ( MIN ( 'Power BI users extract'[Date] ) )

VAR MaxYear = YEAR ( MAX ( 'Power BI users extract'[Date] ) )

RETURN

ADDCOLUMNS (

FILTER (

CALENDARAUTO( ),

AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )

),

"Calendar Year", "CY " & YEAR ( [Date] ),

"Month Name", FORMAT ( [Date], "mmmm" ),

"Month Number", MONTH ( [Date] ),

"Weekday", FORMAT ( [Date], "dddd" ),

"Weekday number", WEEKDAY( [Date] ),

"Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1

)

The columns are generated as they should but i want to add the following feature to my dax calendar:

1) I want to see the quarters in the following format = Q1-2019 , Q2-2019

2) Same thing for the months where they would appear as the following = Jan2020, Feb2020 and so one
3) i would also like to create a column called "Period" where i can have the dates as follow = 11-2019, 12-2019 , 01-2020 ...

4) I would like to add the week numbers but not as a simple week number but i want to show them as follow = W1-Jan , W2-Jan and so on

Any input from any of the forum users would be highly appreciated.

Thanks a lot

[Mariusz · Accepted solution]

Hi @Mous007 

Please see the below, not really sure about the period column.

DatesTable = 

--VAR MinYear = YEAR ( MIN ( 'Power BI users extract'[Date] ) )
--VAR MaxYear = YEAR ( MAX ( 'Power BI users extract'[Date] ) )
VAR MinYear = YEAR ( TODAY() )
VAR MaxYear = YEAR ( TODAY() )
RETURN
ADDCOLUMNS (
    CALENDAR( DATE( MinYear, 1, 1 ), DATE( MaxYear, 12, 31 ) ),
    "Calendar Year", "CY " & YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "MMM" ),
    "Month Number", MONTH ( [Date] ),
    "Weekday", FORMAT ( [Date], "ddd" ),
    "Weekday number", WEEKDAY( [Date] ),
    "Quarter", "Q" & QUARTER( [Date] ),
    "Month Quarter", FORMAT( [Date], "MMM-YYYY" ),
    "Year Quarter", "Q" & FORMAT( [Date], "Q-YYYY" ),
    "Week", "W" & FORMAT( [Date], "WW-MMM" )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Mous007]

hi @Mariusz , Thank;s a lot.  I have modified my calendar dax according to what you proposed. The only line where i had an error was the Quarter one which i have left out for now.