Data Refresh

[hweaves12]

I'm using the DimDate calendar from Ruth at Curbal which generates my calendar for me and I've added a column to that table IsWorkingDay which says if its a Saturday or a Sunday then 0 else its a 1.

The DimDate calendar generates all dates from a given start date up to today's date.

So I can calculate the number of working days so far this month with the following

Working Days So Far This Month = TOTALMTD(SUM(DimDate[IsWorkingDay]),DATESMTD(DimDate[Date]))

However I needed to work out the total number of working days for the whole month  so I created a table (Working Days).

Three columns StartOfMonth, NumberofDays and MonthName.

So something like 01/01/2017,20,Jan then 01/02/2017,20,Feb then 01/03/2017,23,Mar and so on for the rest of the year.

I've created the relationship linking the StartOfMonth date in the Working Days table to the Date field in the DimDate table.

One of the measures I created on the WorkingDays table is as follows.

Working Days In Month = TOTALMTD(SUM('Working Days'[Number]),DATESMTD(DimDate[Date]))

ALL works well on the desktop version with no issues and I can publish and manually refresh no problems.

However when I schedule a refresh everything works apart from this measure and since I use this measure a lot in the report a lot of the visuals show (BLANK)

Any help would be greatly appreciated or any suggestions on an alternative way of doing working days in a month.

[v-yuezhe-msft]

@hweaves12,

I am not quite sure about your requirement. Why not directly use the “Working Days So Far This Month” measure to create visuals?  And in your Working Days In Month measure, you use the Number field, is the field same as NumberofDays?

Regards,
Lydia

[hweaves12]

Not quite Lydia.

To let you understand what I am doing I'm trying to work out a simple forecast.

Sales for the month  / working Days So Far  *  total working days in month.

My calendar only goes up to todays date so if I set the week days to a 1 and the weekends to a 0,  I can calculate the number of working days from the beginning of the month to today no problem.

However since my dates only go up to today I cant use my dates table to work out the total number of working days this month.

Thats why I created a seperate table to hold that value.

But I think the real issue here is why should an immediate dataset refresh work ok with no issues but a scheduled refresh, although it says its completed, not be able to calculate one of my measures.

[v-yuezhe-msft]

@hweaves12,

We will appreciate it if you can share the PBIX file for us to analyze.

Regards,

[hweaves12]

Many, many, thanks for your help, i will supply it but at the moment Im trying to strip it back step by step to see where it fails.

Im still not exactly sure why the desktop version works and when published to the web part of it fails on a refresh. Its nothing to do with the data connections as I can edit the report online and pull data from any parts of the dataset.

Anyway I will let you know how I get on.

Again many thanks.