MTD values only show with date dimension, not in cards

[frankherfert]

Hello,

first time poster here and quite new to DAX/PowerBI.

I have a sales table with dates and amount-values. Next to that is a budget table with dates and plan-amount-values. 

Both are linked in the relationship window.

I am trying to show the MTD-Values for sales on a card and use the same formula in a plan-actual-comparison.

So far I can only get one case to work. In a PowerBI book it was recommended to always use the date-table for the DATESMTD-reference.

SalesMTD = CALCULATE(SUM(Sales[Amount]);DATESMTD(AllDates[Date]))

When I do this, data shows in a table with days in columns but it does not show on a card (most important values are shown on cards in my reports) or anywhere else when no data-dimension is used.

When I change to formula to DATESMTD(Sales[Date]) values show on a card but not on a table.

The same applies to a formula for budget-actual percentage. If I reference the AllDates-table in both formulas it works, if changed to their individual date columns the percentage doesnt work but I can show values without using date dimensions.

Does anyone know how to fix this? Do I need to use two formulae, one for tables and one for standalone uses like in cards?

Thank you in advance.

[zkazimov]

Hey Guys,

I have same issue with measures with dates from calendar not working in Cards and Gauge Visuals.  It works in Charts, Tables, etc. 

My calendar table have correct relationshipe with sales date based on date field.

As you can see from the image below, I have applied page filter to tell MTD MARGIN measure what dates i want in DATESYTD. Please help. Thanks. 

[Ashish_Mathur]

Hi,

Share the file please.

[zkazimov]

Hi Ashish, 

thanks for checking on this file. this drives me crazy. I attached it. see how even last month sale on the table is not listed on the same row for sepetember.

Power BI File

[Ashish_Mathur]

Hi,

There is no visual in that file.  Please check once again.

[zkazimov]

Are you able to access data in sales table?

[Ashish_Mathur]

No.  There is no data there - just headings.

[zkazimov]

ok, sorry i thought once it is a template file, copy of data is store din the file. here is link for sample data. please replace data source with this one. 

Sales file

[Ashish_Mathur]

Hi,

If i do that, i will have to recreate all your visuals and filters.  Please share the .pbix file.

[zkazimov]

here you go.

PBX File

[Ashish_Mathur]

Hi,

Your MTD Margin measure should simply be

=SUM('sales data'[CHARGES])

The card visual works just fine now

[zkazimov]

I need more than simple SUM. I need functions to show time intelligence values like Last Month, This Month, and Variance.

Have you tired to show Last Month Margin on the card or on the table on the same line with September?

[Ashish_Mathur]

Hi,

it is all doable easily now.  The formula for LM MTD margin should be

=CALCULATE([MTD MARGIN],PREVIOUSMONTH('calendar'[DATES]))

Here's the screenshot

[zkazimov]

This is so weird. Can you send me back PBIX file. I am not getting same results as you using same calculation see below.

[zkazimov]

i got it now. my mistake was to use calendar[DATES].[Date] instead of calendar[DATES] in he calcualtion.

do you what is difference? how . [Date] makes so much difference?

[Ashish_Mathur]

The PREVIOUSMONTH() function accept a colun of dates as an input.  That column is CALENDAR[DATES].

Please mark my relevent reply as Answer.

[zkazimov]

Ashish,

Thanks a lot for time spent on this issue. Appreciated.  

[Ashish_Mathur]

Your formula is not the same as mine.  Review my formula in the previous post carefully.

[greggyb]

1. Make sure your relationship is defined on the date field in your date dimension and your fact table, not on some integer date key.
   I just wrote a pretty lengthy reply to another thread about why this is important in Power BI.
2. Time intelligence functions work within a filter context. If you do not have any filter context (visual brushing and linking, slicers, visual-, page-, or report-level filters), then you'll likely see unexpected behavior.
   DATESMTD() does not understand what today's date is. All it does is look at its current filter context and return all dates that share a month with the current month in context, and are less than the last date in context.
   If more than one month is in context (e.g. at a year subtotal level), then DATESMTD() works based off of the last month in context. So at the year subtotal level, DATESMTD() returns identical values as it would in December of that month.
   Technically, it only knows how to look for the last month in context. When only one month is in context, it is by definition the last month in context.
   If there's no context on the date, then DATESMTD() returns all dates in the last month in context. Thus, when used in a card without any filter context, DATESMTD() is returning all the dates in the last month that exists in your date dimension.
   You need to apply a context to the card. Ideally you want to apply the context of today's date, so that you don't have to update any filters. To do this you'll need a helper column, TodayFlag in your date dimension. You can add a column with the following DAX to cause this field to have a value of True for today's date and False for all other dates. With this field, you can set a visual-level filter on the card(s) to TodayFlag = True

   // DAX
   // TodayFlag will be updated every time the
   // model refreshes, so you never have to change
   // your filter
   TodayFlag =
   AllDates[Date] = TODAY()