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
dgwilson
Resolver III
Resolver III

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

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.

View solution in original post

11 REPLIES 11
amitchandak
Super User
Super User

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?

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

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.

 

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

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).

 

Here is the link to the reproduceable case.

 

Chart Test Download

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))

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

@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])

Thanks Jeff...

 

Yea... I had an implementation MAX(Calendar[Date]) ... and it sort of worked....  well it did work... the BUT in the story is that my calendar goes way past today.... like one year past... and it means the test doesn't work. The repro that i've uploaded reflects this.

 

 

- David

 

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.

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.