topic Re: Dax in DAX Commands and Tips

Dax

Alex_Hasan — Wed, 11 May 2022 08:44:44 GMT

Hello good people, I was given the assignment below to solve this. As a newcomer in PBI, I find it confusing. I tried hard and solved the problem, but it seems I actually messed up with the solution. Now I've submitted the solution file and I will be happy if someone kindly solves the problem so that I can learn from my mistakes. Could anyone please help me with the solution?

https://drive.google.com/file/d/1FhyexInp3ghfep3BG0wnPUoFX_Ki1O9m/view?usp=sharing

Case:

Executives at a local grocery distributor are looking for some analysis on the data they have provided in the tab named SAP Data Extract.
1. Assume the current date is November 15, 2016.
- Use a Calendar Dimension ( PowerQuery)
- Achieve this by using a slicer, so we can dynamically check another date
2. We would like to see the current year-to-date growth rate.
3. We would like to see that using Calcualte & DATESYTD () function
4. Call out any anomalies in the data as well as present any visuals that help them make better choices in the direction they want to take.

Re: Dax

amitchandak — Wed, 11 May 2022 10:20:09 GMT

Alex_Hasan , if you have date table marked as date table, then you can create a measure like example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

refer if needed

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Re: Dax

Alex_Hasan — Wed, 11 May 2022 12:00:19 GMT

Hi amitchandak , what would be the dax for total sales here? Because I need to calculate it properly.

Is it SUM(Sales[Net Sales])?

Re: Dax

v-chenwuz-msft — Mon, 16 May 2022 10:02:55 GMT

Hi Alex_Hasan ,

Did you want sum all the sales until the selected date?

Maybe you can try this measure to do that.

Measure = CALCULATE ( [Total sales], FILTER ( ALLSELECTED ( Dim_Date ), [Date] <= MAX ( 'Dim_Date'[Date] ) ) )

Best Regards
Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.