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
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.
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.
Hi,
Share the file please.
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.
Hi,
There is no visual in that file. Please check once again.
Are you able to access data in sales table?
No. There is no data there - just headings.
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.
Hi,
If i do that, i will have to recreate all your visuals and filters. Please share the .pbix file.
Hi,
Your MTD Margin measure should simply be
=SUM('sales data'[CHARGES])
The card visual works just fine now
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
This is so weird. Can you send me back PBIX file. I am not getting same results as you using same calculation see below.
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.
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.
// DAX // TodayFlag will be updated every time the // model refreshes, so you never have to change // your filter TodayFlag = AllDates[Date] = TODAY()
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
84 | |
76 | |
64 |
User | Count |
---|---|
148 | |
115 | |
110 | |
102 | |
95 |