Report with WTD,MTD,QTD and YTD, to support stacked column chart

[AK1982]

Hi,

I am trying to create a report to display values a below, ending up with below result.

Could you please help out to resolve the issue.

Source Data:

Sale Date	sale_status
2021-01-11 00:00:00.0000000	R
2021-01-11 00:00:00.0000000	R
2021-01-11 00:00:00.0000000	R
2021-01-14 00:00:00.0000000	R
2021-01-14 00:00:00.0000000	S
2021-01-14 00:00:00.0000000	S
2021-01-14 00:00:00.0000000	S
2021-01-14 00:00:00.0000000	S
2021-01-17 00:00:00.0000000	R
2021-01-17 00:00:00.0000000	S

Data Output required:

LABEL	Net_Sales	retruns
WTD	72	20
MTD	380	88
QTD	1208	196
YTD	1716	162

netsales = total count of sale_status

retruns = total count of retruns from sale_staus ='R'

result i am ending up:

LBL	MTD_N1	QTD_N1	WTD_N1	YTD_N1
MTD	338	1166	53	1674
QTD	338	1166	53	1674
WTD	338	1166	53	1674
YTD	338	1166	53	1674

[audreygerred]

Can you please share a pbix with your measures, joins, fact and dim tables, etc? This will help to troubleshoot the issue.

[AK1982]

thanks for respone, please find the file here https://drive.google.com/file/d/1eGt0eEtaVytc3o5ohTbc4z8iPyNYHEwP/view?usp=sharing 

[audreygerred]

Hello, I am assuming that 1 is a return in the field iscredit. If that is the case, I suggest the following:

• Connect Date from dimdate to the date field in your fact table (sales_units)
• Mark datedim as a date table
• Convert iscredit to a text field so that we can use it to create a filtered measure
• Create a measure for Net Sales: NetSales = COUNT(sales_units[iscredit])
• Create a measure for Returns (using quick measure for filtered value) and filter to the 1 in is credit. Should return a measure like this: Returns = CALCULATE([NetSales], 'sales_units'[iscredit] IN { "1" })
• Create MTD, YTD, and QTD measures using quick measures from NetSales and Returns
  • Here is one as an example: NetSales MTD = TOTALMTD([NetSales], 'dimdate'[Date])

Based on the data that was in the file, here is what I get for counts of net sales (assuming to count all rows of iscredit) and returns (assuming to count all rows that have a 1 for the value in iscredit) when I am filtered to 2023 and Q3.

Here are some links that should help when you move onto the week measures you will need, since week is not part of the built in time-intelligence:

https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/

https://www.daxpatterns.com/week-related-calculations/

And here is a blog post I wrote about Date tables with a link to the one I love using the most: http://powerbiwithme.com/2023/08/01/the-custom-date-table-edition/ 

Please mark as colution is this solves your issue.

[AK1982]

Hi ,

Thank you very much for the solution and Sorry for my delayed reply, I am able view the data, but unable to create below chart with the data in PBI, could you please guide me here.

[audreygerred]

Did you create all of the necessary tables, joins, and measures? If so, select the viz you want and then add the measures and filters.

[Greg_Deckler]

@AK1982 These videos have all of those calculations done multiple different ways and more. 

Also, see these links for more details.

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...