Calendar Function not working with new version of power BI

[Anonymous]

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?

[Ashish_Mathur · Accepted solution]

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

[v-chuncz-msft]

@Anonymous,

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

[Anonymous]

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

From Excel

[Anonymous]

Sorry, correct Excel table

[v-chuncz-msft]

@Anonymous,

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

[Anonymous]

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

Regards

[Ashish_Mathur]

Hi @Anonymous,

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

[Anonymous]

Hi, try this link.

https://drive.google.com/file/d/0B2MHdXJPrLexZEtXd3FrVTAzbm8/view?usp=sharing

[Ashish_Mathur · Accepted solution]

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

[Anonymous]

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

Regards

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

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.

[Anonymous]

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.

[Ashish_Mathur]

You are welcome.