Creating YTD/QTD/MTD Slicers

[andrew5083]

Hello All,

I am attempting to create an easy way for users to select YTD/QTD/MTD options from a slicer.  The main issue is when I pull the calculated table that stores the slicer data into Power BI Desktop, it is throwing an indistinct error stating the table has no data despite the table showing data in it on the SSAS side.  I'm stumped here and would appreciate any advice.

Relationship to date table

                  

Sample of data in TIS Date Invoiced table in SSAS Tabular (calculated table)

Dax used:

= 
VAR TodaysDate = TODAY()
VAR YearStart = CALCULATE(STARTOFYEAR('Date'[Full Date]), YEAR('Date'[Full Date]) = YEAR(TodaysDate) )
VAR QuarterStart = CALCULATE(STARTOFQUARTER('Date'[Full Date]), YEAR('Date'[Full Date]) = YEAR(TodaysDate), QUARTER('Date'[Full Date]) = QUARTER(TodaysDate) )
VAR MonthStart = CALCULATE(STARTOFMONTH('Date'[Full Date]), YEAR('Date'[Full Date]) = YEAR(TodaysDate), MONTH('Date'[Full Date]) = MONTH(TodaysDate) ) 
VAR Result =

UNION (
    ADDCOLUMNS(
        CALENDAR( YearStart, TodaysDate),
        "Selection", "YTD"
    ),
    ADDCOLUMNS(
        CALENDAR( QuarterStart, TodaysDate),
        "Selection", "YTD"
    ),
    ADDCOLUMNS(
        CALENDAR( MonthStart, TodaysDate),
        "Selection", "YTD"
    )
)
RETURN
Result

In Power BI these columns are incorrectly being classified as numeric despite these being classified as "Date" and "Text" respectively in SSAS.

Power BI Error:

Feedback Type:
Frown (Error)

Timestamp:
2021-06-28T16:31:53.4745107Z

Local Time:
2021-06-28T11:31:53.4745107-05:00

Session ID:
68a49f03-789b-43d5-8717-b2e5d9ba17b2

Release:
May 2021

Product Version:
2.93.981.0 (21.05) (x64)

Error Message:
The query referenced calculated table 'TIS Date Invoiced' which does not hold any data because there is an error in its expression.

OS Version:
Microsoft Windows NT 10.0.19042.0 (x64 en-US)

CLR Version:
4.7 or later [Release Number = 528372]

Peak Virtual Memory:
38.3 GB

Private Memory:
479 MB

Peak Working Set:
798 MB

IE Version:
11.789.19041.0

User ID:
525ca465-21b7-4456-95e2-264e790dffd8

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

AS Live Connection:
True

Performance Trace Logs:
C:\Users\a_acason\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip

Enabled Preview Features:
PBI_JsonTableInference
PBI_NewWebTableInference
PBI_ImportTextByExample
PBI_ExcelTableInference
PBI_eimInformationProtectionForDesktop
PBI_cartesianMultiplesAuthoring

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_dataPointLassoSelect
PBI_compositeModelsOverAS
PBI_dynamicParameters
PBI_enhancedTooltips

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
100%

Supported Services:
Power BI

Formulas:

section Section1;

[andrew5083 · Accepted solution]

Hey @v-kkf-msft,

I was able to confirm the calculated table was available in the Tabular DB.  For whatever reason, I could not get Power BI to recognize this table even after running 'Process Full' on the DB - though I've never had an issue with other calculated tables.

I ended up creating the calculated table as a view in SQL and it's working perfectly now.  ¯\_(ツ)_/¯

[v-kkf-msft]

Hi @andrew5083 ,

I think this is caused by the calculated table, please check if the calculated table has been successfully deployed to SSAS.

And you can refer to:

https://support.microsoft.com/en-us/topic/kb2880071-fix-error-messages-when-you-perform-a-processrec... 

https://community.powerbi.com/t5/Power-Query/SSAS-with-Direct-Query-and-Calculated-Columns/m-p/69087... 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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