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.
I have a set of calculations that worked fine until May 1 and then, they broke. The data they build on is correct and displays in the multilevel card (placed there to check the numbers. Here are my measures and the results.
# Days in Month =
CALCULATE (
DISTINCTCOUNT ( DimDate[DateKey] ),
DATESBETWEEN ( DimDate[DateKey], [First Day This Month], [End of Month] ),
FILTER ( DimDate, [Is Weekend] = "No" )
)
# Days MTD =
CALCULATE (
DISTINCTCOUNT ( DimDate[DateKey] ),
DATESBETWEEN ( DimDate[DateKey], [First Day This Month], TODAY () ),
FILTER ( DimDate, [Is Weekend] = "No" )
)
Days Remaining in Month =
[# Days in Month] - [# Days MTD]
Any ideas?
Proud to be a Super User!
Solved! Go to Solution.
Found it 🙂
There are invisible Slicer on your page "Work in progress"
Go to the View menu, Check "Selection Pane", make both slicers visible, deselect "2018-04" 😉
Cheers
Tom
@kcantor Please check the selection pane
Menu: View --> Check "Selection Pane",
on the file you provided are invisible slicer
Yes. This particular data model contains dates all the way through 01/01/2025. In addition, I double checked the weekend flag to determine that there was no glitch marking every day as a weekend date.
This calculation has worked for more than a year. I am completely stumped. There are also no filters involved on dates except to filter the year to last year or this year which should not interfere in this calculation set.
Proud to be a Super User!
I can share the file because I dropped everything except my date table.
Still doesn't work but it also wasn't dependent on anything in the other tables.
Here is a link:
https://www.dropbox.com/s/5nyypnzlosixu0c/InsideSalesNetInvoiced%20date.pbix?dl=0
Proud to be a Super User!
Hi @kcantor
Something strange is happening with your date table. I have selected all values as a table and I can only see data for April only. Can you double check your data source and perhaps refresh the date dim. Can you also do the same as I have done in the image below, do you see only dates for April 2018?
Thanks
shebr
Even with the new Query version that table does that. In the model itself, it is fully populated. There are no filters on the page or visual.
Proud to be a Super User!
@kcantor Please check the selection pane
Menu: View --> Check "Selection Pane",
on the file you provided are invisible slicer
Hey, @kcantor
thanks for providing your pbix
In your PBIX I encountered the same as @shebr does, I'm just able to see dates for April 2018 on your report page: Work in Progress.
Even if there is no page level filter applied
If recreate you Multi-Row Card visual on a new page everything seems to work fine 😞 🙂 For whatever reason.
This is odd.
Regards
Found it 🙂
There are invisible Slicer on your page "Work in progress"
Go to the View menu, Check "Selection Pane", make both slicers visible, deselect "2018-04" 😉
Cheers
Tom
wow. Just Wow. I do not recall adding hidden filters. I guess my partner in BI must have doinked with this when I was on vacation.
Going to have to discuss that.
Thank you @TomMartens I never even thought to look there.
Proud to be a Super User!
You're welcome!
I guess that's what vacations are for, providing time to others for getting you irritated:-)
Cheers
Tom
Hey @kcantor this is really odd.
Here is the link to my pbix file
I recreated your calculations in my sample file (adjusted it slightly due to the fact that my Calendar has a column "Is Workday", and it seems to work as we all would expect:
Maybe you will give my sample file a try, have a look at page 1 🙂
Regards
Tom
I used your calculation in my file. Fun fact, this had been working so long that var was not active when I built it. Your calculation does not work in my date table. I have no filters at all as I stripped everything from the pbix except the date table. That appeared to narrow it down to my date table. It pulls from an excel file that is used in my office. I built a new table in Query and it still didn't work. Here is that query:
let
Source = #date(2015,1,1),
Custom1 = List.Dates(#"Source", Number.From(#date(2025,1,1)) - Number.From(#"Source"),#duration (1,0,0,0)),
#"Sorted Items" = List.Sort(Custom1,Order.Descending),
#"Converted to Table" = Table.FromList(#"Sorted Items", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Inserted Day of Year" = Table.AddColumn(#"Changed Type", "Day of Year", each Date.DayOfYear([Column1]), Int64.Type),
#"Inserted Day" = Table.AddColumn(#"Inserted Day of Year", "Day", each Date.Day([Column1]), Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Day",{{"Day", "DayOfWeekMon"}, {"Day of Year", "DayofYear"}, {"Column1", "DateKey"}}),
#"Inserted Month" = Table.AddColumn(#"Renamed Columns", "Month", each Date.Month([DateKey]), Int64.Type),
#"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([DateKey]), Int64.Type),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Year", "Week of Year", each Date.WeekOfYear([DateKey]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Week of Year", "Day Name", each Date.DayOfWeekName([DateKey]), type text),
#"Added Conditional Column" = Table.AddColumn(#"Inserted Day Name", "Is Weekend", each if [Day Name] = "Saturday" then "Yes" else if [Day Name] = "Sunday" then "Yes" else "No"),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Is Weekend", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Week of Year", "Week of Year - Copy"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Week of Year - Copy", "WeekNumber"}})
in
#"Renamed Columns1"
Proud to be a Super User!
does the DimDate table have values for May 2018?
I assume the second row works cause it's not referencig DateDim but is based on DAX TODAY() fromula, correct?
Thats right, and the measures you have created expects a date to be selected.
Hi @kcantor
Are you using or selecting any filters? If not perhaps add a date filter to the page and select a date and see if that promts the measures to come to life?
What are you trying to achieve?
Let me know how you get on.
Thanks
shebr
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |