cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

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 132019-11-15WebContent Pack: MView 1
User 132019-11-15WebWorkspaceView 2
User 122019-11-15WebContent Pack: M View 2
User 112019-11-15WebWorkspaceView 1
User 272019-11-15WebContent Pack: M View 1
User 362019-11-15WebContent Pack: MView 2
User 262019-11-15WebContent Pack: MView 2
User 4 32019-11-15WebContent Pack: MView 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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

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

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

 

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

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

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

 

Highlighted
Helper III
Helper III

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

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
Highlighted
Super User IV
Super User IV

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

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

 

View solution in original post

Highlighted
Helper III
Helper III

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

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. 

 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors