Forum Discussion
Help Writing Dax Measure for Sales
I have three tables that I am currently working with.
1 - tblSales -
| State | Date | Sales | |
| Washington | 14/01/2023 | 1 | |
| Washington | 28/01/2023 | 1 | |
| California | 13/02/2023 | 1 | |
| Washington | 01/03/2023 | 1 | |
| Washington | 02/03/2023 | 2 | |
| Washington | 03/03/2023 | 2 | |
| California | 03/03/2023 | 2 | |
| California | 04/03/2023 | 1 | |
| Florida | 15/03/2023 | 1 |
2- LaunchDate
| State | SalesStartedDate |
| Washington | 14/01/2023 |
| California | 13/02/2023 |
| Florida | 15/02/2023 |
3- DataCalendar
List of all the dates between the minimun date in the tblSales[Date] up until TODAY().
In this table, I have a column called 'MonthnYear' that retrieves data in the following format the YYYYMM
I would like to write a DaxMeasure that calculate 'Sales + 0' starting from the launchdate onwwards.
When I calculate SUM(tblSales[Sales]) I get the following result for the matrix:
I would like to get the following result
I want sales to show as 0 if there were none for the monthnyear being analysed.
However, if the MonthnYear is before the LaunchDate[SalesStartedDate], sales should show as blank.
Thanks in advance
11 Replies
Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26Rhx4PxzPUpqXypwvs?e=Sl9WW7Hi,
wrap your measure in an if isblank statement e.g
if it's: CALCULATE(SUM(tblSales[Sales])
try: if(isblank(CALCULATE(SUM(tblSales[Sales])),0,CALCULATE(SUM(tblSales[Sales]) )
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
try writing like this
Thank you for your reply!
However, the measure does not take into account the launchdate for each state, and therefore does not help me achieve what I am trying to do.
In a scenario where the launch date is March 15th for Florida, your measure would compute a 0 for the month of February 2023, as per the image below.What Im a trying to do is to compute sales during the month it was launched onwards.
