Forum Discussion

dgwilson's avatar
dgwilson
Icon for Responsive Resident rankResponsive Resident
7 years ago
Solved

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.

 

 

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

 

 

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

 

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

     

8 Replies

  • 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's avatar
      dgwilson
      Icon for Responsive Resident rankResponsive Resident

      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.

       

       

      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.

       

      - David

      • dgwilson's avatar
        dgwilson
        Icon for Responsive Resident rankResponsive Resident

        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?

         

  • dgwilson's avatar
    dgwilson
    Icon for Responsive Resident rankResponsive Resident

    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.