Data Model Question

[Anonymous]

Hi,

I have a chart I need to create showing the CY FTE, Forecast FTE, and PY FTEs month over month. The CY and PY FTE data is in table 6 in the screenshot and the Forecast FTE data is in table 5 in the screenshot.

I'm having troubles creating a visual with months on the x axis and the values mentioned above (CY FTE, Forecast FTE, and PY FTE) because the dim Date is conneced to Table 5, but not Table 6. Table 6 is connected to table 2 which is then connected to Table 1 (Date table).

Is there any way to use the date table to show all 3 values even with table 2 as a link between table 6 and 1? There are other charts on the dashboard that use the dim Date table so I prefer to use that, but it's not working with the Forecast data since the connecting table is the dimFinancial for that table (Table 6). Any ideas on how to make it work?

[parry2k]

@Anonymous something like this

Measure 6 =
CALCULATE ( 
SUM ( Table6[Amount] ),
CROSSFILTER ( DimDate[Date], DimFiscalPeriod[Date], BOTH ),
DATESYTD ( DimeDate[Date], "4/30" )
)

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

That looks like it's returning more values than before - thanks! What if I need to also incorporate Total YTD for the summed value though? Where would that go within the calculation listed above? Fiscal Year End is 4/30

[parry2k · Accepted solution]

@Anonymous may be the measure you are using from table 6, in that measure force crossfilter direction to both, something like this

Measure 6 =
CALCULATE ( 
SUM ( Table6[Amount] ),
CROSSFILTER ( DimDate[Date], DimFiscalPeriod[Date], BOTH ) 
)

In crossfilter user the columns that are used in the relationship between dimdate and dimfiscalperiod.

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡