Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
frankherfert
Advocate I
Advocate I

MTD values only show with date dimension, not in cards

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.

----------------------------------------------------
New to DAX/Power BI. Not a native speaker.
18 REPLIES 18
zkazimov
Helper I
Helper I

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. 

 

 

2017-09-17_16-21-51.jpg

Hi,

 

Share the file please.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

Hi,

 

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

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Are you able to access data in sales table?

No.  There is no data there - just headings.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

here you go.

 

PBX File

Hi,

 

Your MTD Margin measure should simply be

 

=SUM('sales data'[CHARGES])

The card visual works just fine now

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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?

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

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

2017-09-19_7-29-50.jpg

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?

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

 

Please mark my relevent reply as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Ashish,

 

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
greggyb
Resident Rockstar
Resident Rockstar

  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()

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.