Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all,
I am trying to perform some time intelligence calculations. However I can't seem to get anything to work properly. I have a DateTable, and a SalesHistory fact table. I have a relationship between them on Date and InvoiceDate. On my date table, there is one day for every year from 2008 - 2018. In my fact table, I am only pulling data from 2008 - 2018. In the Power BI Desktop, I'm trying to simply start out by calculating Total Sales, MTD Sales and YTD Sales. I created the measures and placed them on Card visualizations and they show blank. I even tried using a filter on year and month and still get no values. What could I be doing wrong? I have pasted all relevent information below.
Thanks to everyone in advance!
Total Sales = SUM ( SalesHistory[NetAmount] )
Total Sales MTD =
CALCULATE ( [Total Sales], DATESMTD ( DatesTable[Date] ) )
Total Sales MTD =
CALCULATE ( [Total Sales], DATESYTD ( DatesTable[Date] ) )
Solved! Go to Solution.
First of all, great job providing the community with the tools needed to help you troubleshoot!
My best guess is that your SalesHistory[InvoiceDate] is actually a datetime data type instead of just date.
If you have a time portion in the date, it's highly unlikely that the InvoiceDate will match your calendar table...1/1/2018 5:03:14 AM is not the same as 1/1/2018 12:00:00 AM (which is what your calendar date will be).
Don't just change the data type in Query Editor. I've run into instances where this step didn't actually truncate the time properly.
Highlight the [InvoiceDate] column, click on the transform tab, click on the highlighted Date button, and click "Date only".
That should truncate the time portion off.
Have you also marked the date table as a date table, not sure about creating models directly in PowerBi, but Time Intelligence wont work in SSAS unless you mark it first.
Hi,
In addition to what's been suggested, please also ensure that you have written them as measures and not as calculated column formulas.
Hi @Anonymous,
Replace your measure by:
Total Sales = SUM ( SalesHistory[NetAmount] ) Total Sales MTD = CALCULATE ( TOTALMTD([Total Sales], DatesTable[Date] ) ) Total Sales YTD = CALCULATE ( TOTALYTD([Total Sales], DatesTable[Date] ) )
Should work as you need.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsFrom first look your measures look fine. TOTALYTD is just syntax sugar for way you have already done with DATESYTD. Same for TOTALMTD.
So something else is causing issue that would require looking at sample file.
I assume you have looked at fact table and actually have data in 2018? 2017?
First of all, great job providing the community with the tools needed to help you troubleshoot!
My best guess is that your SalesHistory[InvoiceDate] is actually a datetime data type instead of just date.
If you have a time portion in the date, it's highly unlikely that the InvoiceDate will match your calendar table...1/1/2018 5:03:14 AM is not the same as 1/1/2018 12:00:00 AM (which is what your calendar date will be).
Don't just change the data type in Query Editor. I've run into instances where this step didn't actually truncate the time properly.
Highlight the [InvoiceDate] column, click on the transform tab, click on the highlighted Date button, and click "Date only".
That should truncate the time portion off.
Sorry it took so long to reply. But this worked perfectly. I had changed the type, but not under the transform option. Thank you very much. Has saved me much frustration!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |