YTD & MTD calculations showing no data.

[SANDEEPV]

Hi All,

I am new and learning Power BI. I have created a model using mixed mode (few tables are import and few tables are DirectQuery) fetching the data from oracle. I have create a model by joining the fact and dimension tables as shown below. 

 

I am using Power Query to build my data dimension.

I am trying to create few Key measures to compare sales YoY.

I started by writing DAX funtions as below.

NET_SALES = sum(FCT_INVOICED_SALES[NET_SALES_AMOUNT])

YTD_NET_SALES = TOTALYTD('Key Measures'[NET_SALES],'DIM_DATE'[Date])

MTD_NET_SALES = TOTALMTD('Key Measures'[NET_SALES],'DIM_DATE'[Date])

when i use Net_Sales in a card visualization it shows data but the other 2 measures does not show data as "Blank". 

Appreciate any guidance to resolve the issue.

[SANDEEPV]

@mwegener 

Thank you for suggestion. Yes I have marked my Dim_Date as a Date Table. Also, as per you suggestion i have added a new measure

YTD_NET_SALES_NEW = TOTALYTD([NET_SALES],DIM_DATE[Date])

I still keep getting the blank data. Do i need to refresh the data to be able to see the value for the measure?

[mwegener]

Hi @SANDEEPV ,

did you set a filter on the date table?

This is necessary to determine the month for the MTD calculation.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[SANDEEPV]

Hi @mwegener 

Could you let me know how to do this. I tried putting the filter for the current month and year in the filters for the Tile visualization but that did not help. 

[mwegener · Accepted solution]

Hi @SANDEEPV 

try this out.

PBIX

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[SANDEEPV]

Hi @mwegener ,

I was able to make changes to my model based on your suggestion and was able to get the correct values for MTD. 

Appreciate all your assistance.

[mwegener]

Hi @SANDEEPV ,

did you mark your table DIM_DATE as date table?

https://docs.microsoft.com/en-us/power-bi/desktop-date-tables

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[mwegener]

By the way...

Naming Convention
o Measures do not belong to a table
    • Avoid table name
    • [Margin%] instead of Sales[Margin%]
    • Easier to move to another table
    • Easier to identify as a measure
o Thus
    • Calculated columns --> Table[Column]
    • Measures --> [Measure]