Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am using date table which has future dates on it. So in order to do time intelligence calculation, I have to use an additional calculated column with date table. To use standard time intelligence functions I have created following calculated column.
DatesWithSales =
DimDate[Date] <= MAX ( FactPrimarySales[Date])
With above-mentioned column, I am using the following measure.
MTD PRI =
CALCULATE([Total PriSales Value],
CALCULATETABLE(DATESMTD(DimDate[Date]),DimDate[DatesWithSales]=True))
With the above measure following results get from Dax studio
Another method is, use of different calculated column.
Up to current month =
Var Today = TODAY()
return DATEDIFF(DimDate[Date],Today,MONTH)
Measure use with the above-calculated column is
MTD PRI = CALCULATE(SUM(FactPrimarySales[NetValue]),DimDate[Up to current month] = 0)
Dax studio result for that is
So standard time intelligence function's server timing in Dax studio give with higher row value but other measure give fewer Rows and KB value. So which measure should I use ?. Can you please justify your answer.
Thank you
@Anonymous but it is not only DATEMTD you are using, you are having DATESIWTHSALES that is doing the scan as well, again it goes down to how you are using it. Again there is nothing standard, it depends how efficiently and effectively you use these functions.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Dear @parry2k
I am using "DatesWithSales" calculated column since dimDate table having future dates. Can you suggest a measure to calculate MTD value?
Thank you
Hi @Anonymous ,
Please try to create a measure as below to get MTD:
MTD PRI = TOTALMTD ( [Total PriSales Value], DimDate[Date] )
Besides that, you can refer the content in the following links to get it.
Calculating MTD, QTD, YTD, Running and Cumulative Total in Power BI
UNDERSTANDING TOTALMTD,TOTALQTD,TOTALYTD
Show MTD, QTD & YTD Calculations To Current Date in Power BI w/DAX
Best Regards
Can not use the measure like that since dates table contains future dates.
Hi @Anonymous ,
Please try to update the formula of measure as below:
MTD PRI = TOTALMTD ( [Total PriSales Value], DimDate[Date], FILTER ( 'DimDate', 'DimDate'[Date] <= TODAY () ) ) |
If the above one still can't get the correct result, please provide some sample data and expected result with details. Thank you.
Best Regards
@Anonymous Question is not a standard and non-standard measure, it is how the measure is done. I don't know what is your definition of standard measure. In the first measure, you have multiple scans on the data table, and in the 2nd measure, you can see there is only one scan, you can easily see what the SQL queries are going.
Not sure what else to tell. Although your first measure is not well written.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Dear @parry2k
What I meant by standard measure is using Dax given time intelligence function like DATESMTD, DATESYTD. Could you please show me how to write the first measure in well?
@Anonymous , Based on what I got.
I think in both cases you are trying not to get dates beyond today or max fact date?
you can get like this example. No need for a column
MTD QTY forced=
var _max = today() //maxx('order',[Order date])
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESMTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESMTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALMTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))
dear @amitchandak
Due to unavoidable circumstances, I have to choose from above mentioned two measures, therefore from measured that I have given which one is better. Should I go with standard time intelligence function or other one?
@Anonymous , go with standard time intelligence, the first one
Dear @amitchandak
Since standard functions show higher Rows and KB number in Dax Studio, could you please explain the advantages of the using of standard function.
Thank you
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |