cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kcantor
Community Champion
Community Champion

Calculation not working this month. Worked other months -- Days in Month

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]

 

days in month.JPG

Any ideas?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




2 ACCEPTED SOLUTIONS

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 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

@kcantor Please check the selection pane

Menu: View --> Check "Selection Pane",

on the file you provided are invisible slicer



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

17 REPLIES 17
TomMartens
Super User
Super User

Hey,

just to be safe, your table DimDate contains record for this month?
Please excuse, if this sounds like very silly question.

Regards
Tom


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
kcantor
Community Champion
Community Champion

@TomMartens

@Stachu

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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @kcantor

 

Are you able to share you pbix file perhaps?

 

Thanks

 

shebr

kcantor
Community Champion
Community Champion

@shebr

@TomMartens

@Stachu

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





Did I answer your question? Mark my post as a solution!

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

 

dateerror.JPG

kcantor
Community Champion
Community Champion

@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. 





Did I answer your question? Mark my post as a solution!

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

kcantor
Community Champion
Community Champion

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.

 





Did I answer your question? Mark my post as a solution!

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens Good find!

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:

image.png

 

Maybe you will give my sample file a try, have a look at page 1 🙂

 

Regards

Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
kcantor
Community Champion
Community Champion

@TomMartens

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"





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Stachu
Community Champion
Community Champion

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?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Thats right, and the measures you have created expects a date to be selected.

shebr
Resolver III
Resolver III

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!