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

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

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

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.

 

d_gosbell
Super User
Super User

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?

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

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

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.

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

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

 

 

 

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.