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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Which measure is better

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

mtdHA.PNG

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

mtdHemas.PNG

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

12 REPLIES 12
parry2k
Super User
Super User

@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.

Anonymous
Not applicable

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

Time intelligence functions

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-yiruan-msft 

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
parry2k
Super User
Super User

@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.

Anonymous
Not applicable

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
Not applicable

@parry2k 

@mahoneypat 

@vphillip 

@v-jayw-msft 

could you please provide me an answwer.

amitchandak
Super User
Super User

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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