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!

Clustered Column w/Current & Previous Period Calcs Totals Inaccurate

Hello,

I have a clustered bar column which displays a Count of all Customers for Current Year vs Previous Year.

The Counts are filtered by calculated date ranges.

The Counts are accurate only when I show the year of each (highest drill up) on the bar chart.

If I drill down the date heirarchy to the Month level, it shows monthly counts which are almost the equivalent of the yearly counts.

Else the calculations work well on the year to year comparison, but the formula's should also work on the month to month and they are not. See screenshot below... I think this is a visual problem. I am pretty sure my calcs are working properly.

 

Month.PNGYear.PNG

 

The first value for Current Period is just a field with a COUNT DISTINCT added to it and dropped into the visual.

The second field is calculated using Period Start and End using the formula's below.

Because the first field is just a field with DISTINCTCOUNT, this tells me that the issue is with Power BI and not my calcs...

Please correct me if I am wrong, but I think this is a bug.

 

Previous Period Start =
IF( [Current Selection] = "Month", DATE( YEAR( [Current Period Start] ), MONTH([Current Period Start])-[Period Months], DAY([Current Period Start]) ),
IF( [Current Selection] = "Quarter", DATE( YEAR( [Current Period Start] ), MONTH([Current Period Start])-(3*[Period Quarters]), DAY([Current Period Start]) ),
IF( [Current Selection] = "Year", DATE( YEAR( [Current Period Start] )-1, MONTH([Current Period Start]), DAY([Current Period Start]) ),
[Current Period Start] )))
 
Previous Period End =
IF( [Current Selection] = "Month", DATE( YEAR( [Current Period End] ), MONTH([Current Period End])-[Period Months], DAY([Current Period End]) ),
IF( [Current Selection] = "Quarter", DATE( YEAR( [Current Period End] ), MONTH([Current Period End])-(3*[Period Quarters]), DAY([Current Period End]) ),
IF( [Current Selection] = "Year", DATE( YEAR( [Current Period End] )-1, MONTH([Current Period End]), DAY([Current Period End]) ),
[Current Period End] )))
Status: Investigating

@BigPicDeepDive 

 

Really sorry that we are not allowed do that. If so, you may consider open a support ticket in Power BI support. There will be a security method to upload the sample file with issue to do troubleshoot.

 

Best Regards,

Community Support Team _Caiyun

Comments
v-cazheng-msft
Community Support
Status changed to: Needs Info

Hi @BigPicDeepDive 

 

Thanks for your description in details. Could you please provide an upload link of a sameple pbix file with your issue and the version of your Power BI Desktop so we can test at our side to confirm whether it is a bug need to fix?

 

Best Regards,

Community Support Team _Caiyun

BigPicDeepDive
Regular Visitor

I can't post the file publicly, it contains real client data. Is there an email I can share it to instead?

 

v-cazheng-msft
Community Support
Status changed to: Investigating

@BigPicDeepDive 

 

Really sorry that we are not allowed do that. If so, you may consider open a support ticket in Power BI support. There will be a security method to upload the sample file with issue to do troubleshoot.

 

Best Regards,

Community Support Team _Caiyun