@lawada ,
You need to have Date and Time tables.
I don't know how your real data looks like and if your example table is with the same columns as in the real scenario, so I'm not sure if it's the best approach in your case, but you can try to play with this measure:
Measure =
VAR frequency = MAX ( T[frequency] )
VAR countUsers =
CALCULATE (
DISTINCTCOUNT ( T[User ID] ),
CROSSFILTER ( 'Weekday'[Day Name], 'Date'[Day Name], BOTH ),
CROSSFILTER ( T[Week day], 'Weekday'[Day Name], BOTH )
)
RETURN
SWITCH (
TRUE (),
frequency = "weekly", countUsers,
frequency = "biweekly", IF ( ISEVEN ( MAX ( 'Date'[Week of Year] ) ), "", countUsers ),
""
)
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
@lawada ,
Concerning 1:* relationship with Time table - it's ok. It's 1:1 here due to the data itself.
I cannot share the file, but I can share the PQ scripts:
1) Time
let
Source = List.Times(#time(0,0,0),24*60*60,#duration(0,0,0,1)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Time"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Time", type time}}),
#"Inserted Start of Hour" = Table.AddColumn(#"Changed Type", "Start of Hour", each Time.StartOfHour([Time]), type time)
in
#"Inserted Start of Hour"
2) Date
let
Today=Date.From(DateTime.LocalNow()), // today's date
FromYear = 2020, // set the start year of the date dimension. dates start from 1st of January of this year
ToYear=2022, // set the end year of the date dimension. dates end at 31st of December of this year
FromDate=#date(FromYear,1,1),
ToDate=#date(ToYear,12,31),
Source=List.Dates(
FromDate,
Duration.Days(ToDate-FromDate)+1,
#duration(1,0,0,0)
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Month", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Day of Week", "Month Name", each Date.MonthName([Date]), type text),
#"Added Custom4" = Table.AddColumn(#"Inserted Month Name", "Month Year", each Text.Start([Month Name], 3) & "-" & Text.End(Text.From([Year]),2)),
#"Inserted Week of Year" = Table.AddColumn(#"Added Custom4", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type)
in
#"Inserted Week of Year"
3) Weekday
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
@lawada , please, check again the script. I've added the last line of code.
Details:
Since you wanted to start calculation from today, I've looked and current week number - if it is even or not. And then just used https://dax.guide/iseven/ function to count values bi-weekly.
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
