Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calendar Function not working with new version of power BI

The following measure is not working in powerbi desktop.

3 Months Unit Sold = CALCULATE([Units Sold],DATESINPERIOD(DimDate[FullDateAlternateKey],LASTDATE(DimDate[FullDateAlternateKey]),-3,MONTH))

 

When the same measure is created in Excel 2016 powerpivot it works.

 

Is this a bug?

 

2 ACCEPTED SOLUTIONS

Hi,

 

Here are the changes i made

 

  1. I edited your date relationship of dates.  I hehashed it to "DueDate column of the FactInternetSalesTable to FullDateAlternateKey column of the DimDate Table.  See image below
  2. I edited your 3 Months Units sold calculated field formula to

 

=CALCULATE([Units Sold],DATESINPERIOD(DimDate[FullDateAlternateKey],LASTDATE(DimDate[FullDateAlternateKey]),-3,MONTH))

 

The correct result is shown below

 

Untitled.pngUntitled1.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Hi,

 

The third last column in your FaceInternetSales table is a proper date.  The relationship should be from that column to the FullDateAlternateKey column of the DimDate column table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

13 REPLIES 13
v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

Please elaborate. You may check the relationship and take a look at this link.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks for the response. Relationships are ok and measures in powerbi and excel exactly the same accessing the same database. See images below. 3 Months Unit Sold is the measure in question

From powerbi

pbiCapture.PNG

 

From Excel

ExcelCapture.PNG

 

Anonymous
Not applicable

Sorry, correct Excel table

ExcelCapture1.PNG

@Anonymous,

 

It would be better to share us the simplified .pbix file. Make sure it is a measure, not calculated column.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Sorry just seeing this. How do I attach a file? I don't see any upload buttons. Using MS Edge

 

Regards

Hi @Anonymous,

 

You will have to upload the file to some file hosting servive and then share the download link here.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi,

 

Here are the changes i made

 

  1. I edited your date relationship of dates.  I hehashed it to "DueDate column of the FactInternetSalesTable to FullDateAlternateKey column of the DimDate Table.  See image below
  2. I edited your 3 Months Units sold calculated field formula to

 

=CALCULATE([Units Sold],DATESINPERIOD(DimDate[FullDateAlternateKey],LASTDATE(DimDate[FullDateAlternateKey]),-3,MONTH))

 

The correct result is shown below

 

Untitled.pngUntitled1.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thanks for your effort and time. Sorry about the late response.

 

Regards

Anonymous
Not applicable

Hi, I just tried your suggestion and it does not work. To begin, the relationship you suggested cannot work as the duedatekey in the sales table is an integer and the fulldatealternatekey in the date table is a date field. When I created this relationship the resulting table is empty.

 

I am using powerbi ver 2.50.4859.782 64-bit (September 2017). What version are you using? Remember I indicated that when I used excel with the same sql 2016 adventureworks DW it works correctly by copying and pasting the measure. This suggests a bug in the version I am using or a problem with my installation running on windows 10 pro.

 

Regards

Hi,

 

The third last column in your FaceInternetSales table is a proper date.  The relationship should be from that column to the FullDateAlternateKey column of the DimDate column table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thanks again. I finally realized that because in power bi you don't have the facility to designate a date table then relationships must be based on date fields for the time intelligence functions to work. A work around I found on the internet proposed by Raz is to add the all(dimdate) at the end of the function and that works as well.

 

 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.