Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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" )
)
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] )
Hi @Mariusz , i have read the article and used ur trick and this is what i have so far:
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" )
)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |