cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Blank values in Year Month column on the web visualisation while all is visible in Power Bi desktop,

Hi All,

 

I've got report showing some sales numbers, it's ordered by the Year Month column. All works fine appart of small displaying issues.

On the Web version of that report the value for the latest two months are blank in Year Month column leaving others values as the should be but the same report deployed in Power Bi Desktop behave as it spous to. I have run out of ideas what can cause this issues so welcom any help.

 

Belowe report on the WEB

 

Report on the WEBReport on the WEB

And the very same one in the Desktop,

 

Report in DesktopReport in Desktop

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
New Member

Re: Blank values in Year Month column on the web visualisation while all is visible in Power Bi desk

Thank you for the quick answer,

I did go through your short example and had a second thought about my code. Initially in my Calendar definition the date range has been static:


Calendar Table = VAR BaseCalendar = CALENDAR(Date(2018,8,14), Date(2020,8,13))

 

In main visalistaion compare last year turnover to the current year (Day to Day). The one of requirement was to show comparing data until the current date, it was achived by using code below:

 

Return
GENERATE(

BaseCalendar,
VAR BaseDate = [Date]
VAR YearDate = YEAR(BaseDate)
VAR WeekNumber = IF(WEEKNUM(BaseDate) = 53, 52, WEEKNUM(BaseDate))
VAR MonthNumber = MONTH(BaseDate)
VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1
VAR WeeKYearNumber = VALUE(YearDate& IF((WeekNumber<10),"0" & WeekNumber, WeekNumber))
VAR QTR = CONCATENATE("Q", ROUNDUP(MONTH([Date])/3,0))
Return ROW(
"Year", YearDate,
"Week Number", WeekNumber,
"Month Number", MonthNumber,
"Month", FORMAT(BaseDate, "mmmm"),
"Year Month Number", YearMonthNumber,
"Week Year Number", LEFT(WeeKYearNumber,4) & " " & RIGHT(WeeKYearNumber,2),
"Year Month", IF((YEAR(NOW())*12 + MONTH(NOW()) -1) >= (YEAR([Date])*12 + MONTH([Date]) -1), FORMAT(BaseDate, "mmm yy"), ""),
"QTR", QTR
)

 

It works great, however I found issues with not displaying the latest Year Month values (March, April, May).
So I did change the range in calendar to:

 

VAR BaseCalendar = CALENDAR( Date(2018,8,14), Today() )

 

and now so far it's works perfectly. All values in 'Year Month' column are now visible.

View solution in original post

4 REPLIES 4
Highlighted
Super User VI
Super User VI

Re: Blank values in Year Month column on the web visualisation while all is visible in Power Bi desk

Hi @GekonPL 

it could be a regional settings issue

how does date for last monthes formatted in data source?


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Highlighted
Community Support
Community Support

Re: Blank values in Year Month column on the web visualisation while all is visible in Power Bi desk

Hi @GekonPL ,

 

I have created a sample file, but didn't meet the issue.

In Power BI DesktopIn Power BI DesktopIn Power BI ServiceIn Power BI Service

 

Please share me your PBIX file, removing sensitive information and replacing real data with dummy data, for specific test.

It is suggested to upload your file to OneDrive for Business and then paste the link here.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
New Member

Re: Blank values in Year Month column on the web visualisation while all is visible in Power Bi desk

Thank you for the quick answer,

I did go through your short example and had a second thought about my code. Initially in my Calendar definition the date range has been static:


Calendar Table = VAR BaseCalendar = CALENDAR(Date(2018,8,14), Date(2020,8,13))

 

In main visalistaion compare last year turnover to the current year (Day to Day). The one of requirement was to show comparing data until the current date, it was achived by using code below:

 

Return
GENERATE(

BaseCalendar,
VAR BaseDate = [Date]
VAR YearDate = YEAR(BaseDate)
VAR WeekNumber = IF(WEEKNUM(BaseDate) = 53, 52, WEEKNUM(BaseDate))
VAR MonthNumber = MONTH(BaseDate)
VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1
VAR WeeKYearNumber = VALUE(YearDate& IF((WeekNumber<10),"0" & WeekNumber, WeekNumber))
VAR QTR = CONCATENATE("Q", ROUNDUP(MONTH([Date])/3,0))
Return ROW(
"Year", YearDate,
"Week Number", WeekNumber,
"Month Number", MonthNumber,
"Month", FORMAT(BaseDate, "mmmm"),
"Year Month Number", YearMonthNumber,
"Week Year Number", LEFT(WeeKYearNumber,4) & " " & RIGHT(WeeKYearNumber,2),
"Year Month", IF((YEAR(NOW())*12 + MONTH(NOW()) -1) >= (YEAR([Date])*12 + MONTH([Date]) -1), FORMAT(BaseDate, "mmm yy"), ""),
"QTR", QTR
)

 

It works great, however I found issues with not displaying the latest Year Month values (March, April, May).
So I did change the range in calendar to:

 

VAR BaseCalendar = CALENDAR( Date(2018,8,14), Today() )

 

and now so far it's works perfectly. All values in 'Year Month' column are now visible.

View solution in original post

Highlighted
Community Support
Community Support

Re: Blank values in Year Month column on the web visualisation while all is visible in Power Bi desk

Hi @GekonPL ,

 

Glad to hear the issue is gone. You may help accept the replies making sense as solution above. Your contribution is highly appreciated.

 

 

Best Regards,

Icey

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Top Solution Authors
Top Kudoed Authors