cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Measure does not display value in card visualisation

Hi there,

 

I have a measure that calculates the cumulative sum until the end of last month. The measure looks like as follow:

 

cumulative sum =
var CurrentDate = MAX('Outages '[Actual Interruption Start Date Time])
Var lastmonth =EOMONTH(TODAY(),-1)
Var _CYMonth = MONTH(TODAY())
Var _RYMonth = if(_CYMonth > 3 && _CYMonth <= 12, _CYMonth - 3,_CYMonth + 9)

 

Return
sumx(FILTER(ALL('Outages'),'Outages '[Actual Interruption Start Date Time]<=CurrentDate && 'Outages '[Actual Interruption Start Date Time ] <=lastmonth && Month(CurrentDate)<_CYMonth ),
[sum_measure])

The above-mentioned measure works fine when I use in the area chart as shown below:
 

Screenshot 2020-10-27 144444.jpg

 

However, if I use the same measure in the card visual, it shows blank:

 Screenshot 2020-10-27 144645.jpg

I noticed that If I remove the colored text from the below measure then it works fine. However, the calculation is different as it starts considering the current month (after removing the colored text). Did someone encounter the same problem? Any help to fix the issue would be really appreciated.

 

cumulative sum =
var CurrentDate = MAX('Outages '[Actual Interruption Start Date Time])
Var lastmonth =EOMONTH(TODAY(),-1)
Var _CYMonth = MONTH(TODAY())
Var _RYMonth = if(_CYMonth > 3 && _CYMonth <= 12, _CYMonth - 3,_CYMonth + 9)

 

Return
sumx(FILTER(ALL('Outages'),'Outages '[Actual Interruption Start Date Time]<=CurrentDate && 'Outages '[Actual Interruption Start Date Time ] <=lastmonth && Month(CurrentDate)<_CYMonth ),
[sum_measure])

 

 

 

 

 
4 REPLIES 4
Highlighted
Super User IV
Super User IV

@leo_89 , Formula seem correct , try this one

 

cumulative sum =
var lastmonth =EOMONTH(TODAY(),-1)

Return
sumx(FILTER(ALL('Outages'),'Outages '[Actual Interruption Start Date Time]<=MAX('Outages '[Actual Interruption Start Date Time]) && 'Outages '[Actual Interruption Start Date Time ] <=lastmonth),
[sum_measure])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

@amitchandak  Thanks for the response, I tried your suggested one. Following is the output for the new formula

 

leo_89_0-1603765664110.png

As you can see it starts showing the value of the Current month (i.e. October) and next month (i.e. November). However, it started showing value on the card visual. As I mentioned in my actual post that this cumulative measure shows the cumulative sum until the end of last month. Don't want to show the current and next month.

Highlighted

@leo_89 , As you have not selected any date on page that is why is showing null on card . Because your formula does not go beyond current month.

Your formula is correct. You need to have filter to see data on card



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

Hello, @leo_89

According to its description, the measure shows the same value as September in October and November. I suggest you use the page filter to filter the months you want to display, and 10,11,12 months will disappear on the chart as well:

v-robertq-msft_0-1603874818916.png

Best regards

Qin Community Support _Robert Team

If this post helps,then consider Accepting it as the solution to help other members find it faster.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors