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

DatesYTD returns Blank for first month

I'm hoping that someone here has seen this before and can advise what is going on.

The DAX pasted below is to figure out the Revenue for the last financial year. The Financial Year runs from 1 July to 30 June. As in the screen shot below July presents no data - Blank - and I do not understand why.

Screen shot below shows the output from the DAX.

 

2019-07-09 09_14_38-Customer Profitability Data - where is July.png

 

This screen shot shows the date table and that FY 2019 does indeed have the correct number of days.

 

2019-07-09 09_13_14-Customer Profitability Data - Calendar.png

 

_Revenue TY test = 
VAR fiscalYear = [Fiscal Year TY]
VAR reportMonthStart = CALCULATE(FIRSTNONBLANK('Date'[Date], 1), ALL('Date'), 'Date'[Fiscal Year] = fiscalYear)
VAR reportMonthEnd = CALCULATE(LASTNONBLANK('Date'[Date], 1), ALL('Date'), 'Date'[Fiscal Year] = fiscalYear)
VAR TFYDatesBetween = DATESBETWEEN('Date'[Date], reportMonthStart, reportMonthEnd)
        VAR DateFilterYTD =
            INTERSECT (
                DATESYTD ( 'Date'[Date], "30 June" ),
                TFYDatesBetween
            )
RETURN
    CALCULATE (
                SUM(EVO_DMT_BUS_CUST_PROFIT_VW[_Revenue]), 
                'Date'[Date],
                DateFilterYTD
               )

I don't have a test case to upload at this time, sorry. The main data set is 19 million rows and 270+ MB of data... and financial ... etc etc etc...

 

And I'm not using TOTALYTD, etc... it's been a nightmare.

 

1 ACCEPTED SOLUTION

Accepted Solutions
dgwilson Member
Member

Re: DatesYTD returns Blank for first month

There is lots of good information in this thread and I don't want to take anything away from the responses as they're good and worth reading.

 

Back tpo the original question as to why one of the rows (July) does not show a total. It is because there are multiple July's. Adding a full date to a table or matrix, allows you to see exactly what the DAX is dealing with... in this case July 2018 and July 2019.

 

I hope this explanation helps someone else in their debug efforts.

 

View solution in original post

8 REPLIES 8
Super User
Super User

Re: DatesYTD returns Blank for first month

Do you have data for July? That first table is the sort of output I would expect if the earliest date in FY 19/20 was some time in Aug 2019.

 

If you drop the EVO_DMT_BUS_CUST_PROFIT_VW[_Revenue] column onto that first table does it show any amount against July 2019?

dgwilson Member
Member

Re: DatesYTD returns Blank for first month

I know July has data... I've been runnign through dates matching/not matching etc... and then did this... added the _Revenue column to the table. I had been considering that there is a bug in Power BI regarding this date handling.

 

This looks like it's suddenly started working... I'm not convinced.

 

2019-07-09 09_14_38-Customer Profitability Data - where is July example.png

 

Now look at the chart below... uses the same measure... and doesn't have July in the Chart. Though the total for August includes the July data... as it does/did in the table above.

2019-07-09 09_14_38-Customer Profitability Data - where is July with Chart.png

 

- David

dgwilson Member
Member

Re: DatesYTD returns Blank for first month

New information...

I added the actual dates to the debug table below... 

where totals are not being displayed for "July" [FY 2019] there is date rows present for FY 2020.

This maybe implies an issue with the date ranges in the measures?

 

2019-07-09 09_14_38-Customer Profitability Data - where is July - New Information.png

Super User
Super User

Re: DatesYTD returns Blank for first month

There are a number of calcs that I can't see the expressions for so it's tricky to tell exactly what is going on and your YTD calc seems quite complicated. If you just use a version like the following does it work any better?

 

CALCULATE (
                SUM(EVO_DMT_BUS_CUST_PROFIT_VW[_Revenue]), 
                DATESYTD ( 'Date'[Date], "30 June" )
          )

Note that you can't really use a month attribute that does not include the year unless your report/visual is also filtered to a specific year, otherwise your "July" figure will include the data from July in every year for which you have data (as will all the other months). I just noticed that in your first post that the year is actual the "Min of Year" not the year column, so it's not filtering therefore there could be multiple years worth of values in each month.

dgwilson Member
Member

Re: DatesYTD returns Blank for first month

This is good to discuss, thank you for the sounding board.

 

I've proven, easily now, that the reason July "appeared" blank was because there were multiple july's from different years. That's been a costly leason in terms of time but a good one. The same thing would occur as well if I had data for August, September, ..., ...

 

I can get the visuals to display correctly if I add a visual filter of Financial Year ... i.e. 2019. That's great news as it means that the dax is probably OK... 

 

HOWEVER. I do not want to put a fixed year on each visual as that will mean they need to be manually changed every year. So my plan... do it in DAX... and this is where it's all falling apart. Somehow I need to wrap the DAX in a fiscal year filter parameter.

 

- David

dgwilson Member
Member

Re: DatesYTD returns Blank for first month

Some success I think. I need to test.

 

_Revenue TY cumulative by Month = 
VAR EndOfLastMonth = [Report_Month]
VAR fiscalYear = [Fiscal Year TY]
VAR reportMonthStart = CALCULATE(FIRSTNONBLANK('Date'[Date], 1), ALL('Date'), 'Date'[Fiscal Year] = fiscalYear)     // get the first date of the fiscal year
VAR reportMonthEnd = CALCULATE(LASTNONBLANK('Date'[Date], 1), ALL('Date'), 'Date'[Fiscal Year] = fiscalYear)        // get the last date of the discal year
VAR TFYDatesBetween = DATESBETWEEN('Date'[Date], reportMonthStart, reportMonthEnd)                                  // returns a table of fiscal year dates

RETURN
    IF ( 
        OR( MIN(  'Date'[Date] ) > reportMonthStart,        // ensures that the data reported is within the target date range - works for charts
            MAX ( 'Date'[Date] ) <= EndOfLastMonth
            ),
        VAR DateFilterYTD =
            INTERSECT (                                     // INTERSECT ( <LeftTable>, <RightTable> ) - Returns the rows of left-side table which appear in right-side table.
                DATESYTD ( 'Date'[Date], "30 June" ),       // Returns a set of dates in the year up to current date.
                TFYDatesBetween
            )
        RETURN
            CALCULATE (
                SUM(EVO_DMT_BUS_CUST_PROFIT_VW[_Revenue]), 
//                'Date'[Date],
                DateFilterYTD
               )
)

 

 

 

Super User
Super User

Re: DatesYTD returns Blank for first month


@dgwilson wrote:

HOWEVER. I do not want to put a fixed year on each visual as that will mean they need to be manually changed every year. So my plan... do it in DAX... and this is where it's all falling apart. Somehow I need to wrap the DAX in a fiscal year filter parameter.

 


There are a couple of approaches you can take here.

 

Firstly we don't tend to use the month name on it's own very much, in our main month column we use the format of MMM yy or a variation of that so that there is a unique month for each year. That naturally prevents the potential for accidentally adding all the "July's" together and also lets you easily build trend graphs over multiple years.

 

But this does not help if you just want to display 12 months for the "current" year. To do this we created a "Relative Year Offset" column in our date table which returns 0 for the current year, -1 for the previous year, -2 for 2 years ago, etc. Then we can simply add this as a filter to the visual, page or report level as required and this offset is dynamically recalculated as part of our data refresh. We calculate this in a view in SQL Server, but you could also calculate this in Power Query or a calculated column.

 

So if you had a financial year column:

Financial Year = if('Date'[Month Number] >= 7, 'Date'[Calendar Year] + 1,'Date'[Calendar Year])

then you could create an offset calculation like the following:

Relative Financial Year Offset = [Financial Year] -LOOKUPVALUE('Date'[Financial Year], 'Date'[Date],TODAY())

Then use this offset column in a filter. We also have month and week offset columns in our date table which we use for things like producing charts for the last 18 months (so we filter for -17 to 0 on the month offset).

 

Then you can create a simple measure that calculates a YTD for any year and just add the extra filter in specific reports.

dgwilson Member
Member

Re: DatesYTD returns Blank for first month

There is lots of good information in this thread and I don't want to take anything away from the responses as they're good and worth reading.

 

Back tpo the original question as to why one of the rows (July) does not show a total. It is because there are multiple July's. Adding a full date to a table or matrix, allows you to see exactly what the DAX is dealing with... in this case July 2018 and July 2019.

 

I hope this explanation helps someone else in their debug efforts.

 

View solution in original post

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)