Fiscal YTD/ Fiscal QTD DAX error

[MissBI_21]

Hi,

Hope someone can help me! 

I have a DAX code (pasted at the bottom) to generate a table with two columns, one with selection that tells you if you're filtering for MTD/YTD/QTD/FYTD/FQTD and the other column with dates.

The error I am recieving is below. I believe this is due to the column 'pipedrive_deals[Time Won/Lost]' containing blank values.

How can I (and where would I) add in a filter that would exclude blank values please?

Thanks in advance.

The code I'm using is here:

MTD/QTD/YTD/FYTD/FQTD =

//Current Year Variables

VAR today = TODAY()

VAR start_of_year = CALCULATE(STARTOFYEAR(pipedrive_deals[Time Won/Lost]), YEAR(pipedrive_deals[Time Won/Lost]) = YEAR(today) )

VAR start_of_quarter = CALCULATE(STARTOFQUARTER(pipedrive_deals[Time Won/Lost]), YEAR(pipedrive_deals[Time Won/Lost]) = YEAR(today), QUARTER(pipedrive_deals[Time Won/Lost]) = QUARTER (today) )

VAR start_of_month = CALCULATE(STARTOFMONTH(pipedrive_deals[Time Won/Lost]), YEAR(pipedrive_deals[Time Won/Lost]) = YEAR(today), MONTH(pipedrive_deals[Time Won/Lost]) = MONTH(today) )

//Fiscal Variables

VAR FiscalMonthEnd = 4

VAR FiscalYearStart =

SWITCH(TRUE(),

MONTH(today) <= FiscalMonthEnd, YEAR(today) - 1,

YEAR(today)

)

VAR CurrentFiscalQuarter =

ROUNDUP(

DIVIDE(

IF(MONTH(today) > FiscalMonthEnd,

MONTH(today) - FiscalMonthEnd,

MONTH(today) + (12 - FiscalMonthEnd)

), 3

), 0

)

VAR FiscalRangeStart = DATE(FiscalYearStart, FiscalMonthEnd +1, 1)

VAR FiscalQuarterStart =

SWITCH(TRUE(),

CurrentFiscalQuarter = 1, DATE(FiscalYearStart, FiscalMonthEnd +1,1),

CurrentFiscalQuarter = 2, DATE(FiscalYearStart, FiscalMonthEnd +4,1),

CurrentFiscalQuarter = 3, DATE(FiscalYearStart, FiscalMonthEnd +7,1),

CurrentFiscalQuarter = 3, DATE(FiscalYearStart, FiscalMonthEnd +10,1)

)

VAR Result =

UNION (

//Current Year Date Ranges

ADDCOLUMNS(

CALENDAR ( start_of_year, today ),

"Selection", "YTD",

"Sort", 1

),

ADDCOLUMNS (

CALENDAR ( start_of_quarter, today ),

"Selection", "QTD",

"Sort", 2

),

ADDCOLUMNS (

CALENDAR ( start_of_month, today ),

"Selection", "MTD",

"Sort", 3

),

//Fiscal Date Ranges

ADDCOLUMNS(

CALENDAR(FiscalRangeStart, today),

"Selection", "FYTD",

"Sort", 4

),

ADDCOLUMNS(

CALENDAR(FiscalQuarterStart, today),

"Selection", "FQTD",

"Sort", 5

)

)

RETURN

Result

[v-zhangti]

Hi, @MissBI_21 

Can you provide a simple PBIX file for testing? Sensitive information can be removed in advance.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@MissBI_21 , You can create normal date calendar and then you can create measure for these MTD/YTD/QTD/FYTD/FQTD 

Then you can use measure slicer or calculation group

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

FYTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"3/31"))
Last FYTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA