Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Mous007
Helper IV
Helper IV

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
Mariusz
Community Champion
Community Champion

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
Mariusz
Community Champion
Community Champion

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

 

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
Community Champion
Community Champion

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

 

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
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.