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

Dont show data on chart forward of today

I have a line chart that shows revenue last year and this year (todate). 

The line for this year shows a for subsequent months - I don't want it to show any values.

I'd like the DAX not to return any values - however I don't know what to perform the test against.

 

Here's the dax I have... it does not do what I want it to do.

_Revenue TY Chart = 
    VAR reportMonth = [Report_Month]
    VAR fiscalYear = [Fiscal Year TY]
    VAR maxMonth = DATE( YEAR(TODAY()), MONTH(TODAY())-1, 01)
//    VAR rptMonth_LastDate = CALCULATE(LASTDATE(EVO_DMT_BUS_CUST_PROFIT_VW[RPT_MONTH]), FILTER(EVO_DMT_BUS_CUST_PROFIT_VW,EVO_DMT_BUS_CUST_PROFIT_VW[FIN_YEAR] = [Fiscal Year TY]))
RETURN
    IF ( 
        maxMonth
         <= reportMonth,        // ensures that the data reported is within the target date range - works for charts
        CALCULATE(TOTALYTD(SUM(EVO_DMT_BUS_CUST_PROFIT_VW[_Revenue]), 'Date'[Date], "30 June"), 'Date'[Fiscal Year]=fiscalYear, ALL(EVO_DMT_BUS_CUST_PROFIT_VW[RPT_MONTH]), EVO_DMT_BUS_CUST_PROFIT_VW[RPT_MONTH] <= reportMonth)
    )

 

Chart with data I don't want.PNG

The horizontal line on the chart above from September through to June - I don't want that to show.

 

- David

 

1 ACCEPTED SOLUTION

Accepted Solutions
dgwilson Member
Member

Re: Dont show data on chart forward of today

Here's some DAX that works.

 

_Revenue TY Chart to fix = 
    VAR reportMonth = DATE(2019, 10, 01)
    VAR FYStart = DATE(YEAR(TODAY()), 7, 01)
    VAR fiscalYear = 2020
RETURN
// This calculate does not work - I think it should... but it doesn't
//        CALCULATE(
//                SUM(FinData[Revenue]), 
////                DATESYTD('Date'[Date], "30 June"), 
//                FILTER('Date', 'Date'[Fiscal Year]=fiscalYear && 'Date'[Date] <= reportMonth)
//            )
    CALCULATE(
                TOTALYTD(SUM(FinData[Revenue]), 'Date'[Date], "30 June"),
                FILTER('Date', 'Date'[Fiscal Year]=fiscalYear && 'Date'[Date] < reportMonth)
            )

Notice the commented out CALCULATE. I think that should work but it does not. That's another mystery to be solved.

 

Screen Shot 2019-09-23 at 4.20.01 PM.png

 

Mission accomplished.

11 REPLIES 11
amitchandak Super Contributor
Super Contributor

Re: Dont show data on chart forward of today

Can you print reportMonth on a card and check the value it is giving. And post that debug. which part is causing the issue. Can you share some sample file after removing sensitive data?

dgwilson Member
Member

Re: Dont show data on chart forward of today

RPT_MONTH is 01-AUG-2019

 

It doesn't matter really what it is... it could be 01-OCT-2019... it just means I want the line for revenue this year to only show 01-JULY-2019 to RPT_MONTH.

 

What I need to be able to pick up on is what is the IF test I need to do so that I only return the results I want. I've previously used MAX( 'Date'[Date] ) ... but that doesn't work because my date table has dates at least one year in advance. I need to know what else is driving the measure (by date) so that I can test against that. That's my theory anyway.

 

- David

amitchandak Super Contributor
Super Contributor

Re: Dont show data on chart forward of today

For max Date use

 

maxx(allselected(date),date[date])

This will ensure it does not go beyond the selected date. In case there is no limit on that then do same on fact.

 

dgwilson Member
Member

Re: Dont show data on chart forward of today

That hasn't worked.

 

and I've just tried:

IF ( SELECTEDVALUE('Date'[Date]) 
         <= reportMonth, 

which hasn't work either... it results in the values for the remainder of the year still showing.

 

I'm going to have to build a repro for testing against. That's going to take a little bit of work. WIll see if I can do it tonight.

 

- David

amitchandak Super Contributor
Super Contributor

Re: Dont show data on chart forward of today

Both MaxMonth and Report are calculated outside calculate. Means both are static values. They need to part of the calculate formula. 

 

Example

 

Sales 10 Days = ( 
VAR _Cuur_start = Max('Compare Date'[Compare Date]) -10
VAR _Curr_END = Max('Compare Date'[Compare Date])
return 
calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _Cuur_start && Sales[Sales Date] <=  _Curr_END )
)

Or this, same table getting filtered on two condition

 

 

Sales Before QTR = 
CALCULATE(SUMx(FILTER(sales,and(Sales[Order_Date]>= STARTOFQUARTER(DATEADD(STARTOFQUARTER(OrderTime[Order Date]),-1,DAY)) && Sales[Order_Date]<= DATEADD(STARTOFQUARTER(OrderTime[Order Date]),-1,DAY),
Sales[Requested_Date]>= STARTOFQUARTER(OrderTime[Order Date]) && Sales[Requested_Date]<= STARTOFQUARTER(OrderTime[Order Date])
)),Sales[Sales]),CROSSFILTER(Sales[Order_Date],OrderTime[Order Date],None))

 

"If" outside calculate is working on the result. Inside calculate is where the logic is working on the table; as per the current calculation (as per my understanding).

 

dgwilson Member
Member

Re: Dont show data on chart forward of today

Here is the link to the reproduceable case.

 

Chart Test Download

amitchandak Super Contributor
Super Contributor

Re: Dont show data on chart forward of today

try

_Revenue TY Chart to fix = 
      VAR reportMonth = DATE(2019, 08, 01)
    VAR fiscalYear = 2020
RETURN
    CALCULATE(TOTALYTD(SUM(FinData[Revenue]), 'Date'[Date],FinData[Date] <= reportMonth, "30 June"), 'Date'[Fiscal Year]=fiscalYear,FILTER(FinData,FinData[Date]<=reportMonth))
dgwilson Member
Member

Re: Dont show data on chart forward of today

This does not work because the RETURN statement needs to have an IF.

When the IF statement is present... and IF there is no data to present for October to June (i.e. nothing is returned) then no data is presented on the chart - i.e. the desired result.

 

By adding in the date checking to the CALCULATE the correct assessment for values are made - however they return a result which is not what is required.

 

- David

JeffWeir Regular Visitor
Regular Visitor

Re: Dont show data on chart forward of today

@dgwilsonI don't have time today to really get to grips with your requirement, but  when I want to only show data up to a particular date I use this:

IF(MAX(Calendar[Date])<=SomeDate, [Some Measure])

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 283 members 2,867 guests
Please welcome our newest community members: