topic Re: YTD/ LTM show correct total but row values missing in DAX Commands and Tips

YTD/ LTM show correct total but row values missing

Dr_BB — Fri, 06 Dec 2024 17:03:22 GMT

Hi,

Thanks in advance for your help!

I want to show YTD/ LTM sales at Store level in a table, however, I am getting correct totals but some rows are missing.

Lets say I select week49, only the stores in week49 will show as individual rows but total will show correct number.

How can I show all the stores selling in YTD time period and not just the selected week? (Example below)

I have created a measure to calculate YTD as follows:

test_ytd =

var yr = SELECTEDVALUE(SALES[YEAR])

var mn = SELECTEDVALUE(SALES[MONTH])

var wk = SELECTEDVALUE(SALES[WEEK])

var sales_ytd = CALCULATE( CALCULATE( SUMX(SALES, SALES[sales])), SALES[YEAR] = yr, SALES[WEEK] <= wk, SALES[MONTH] <= mn )

Re: YTD/ LTM show correct total but row values missing

DataNinja777 — Fri, 06 Dec 2024 23:04:05 GMT

Dr_BB ,

To address your DAX question and show all stores with sales in the YTD time period (not just those active in the selected week), you can modify your approach by ensuring the filter context does not exclude stores that do not have sales in the selected week.

Here's an improved DAX formula to achieve this:

test_ytd = VAR yr = SELECTEDVALUE(SALES[YEAR]) VAR wk = SELECTEDVALUE(SALES[WEEK]) RETURN CALCULATE( SUM(SALES[sales]), ALL(SALES[WEEK]), // Ensures all weeks are considered SALES[YEAR] = yr, SALES[WEEK] <= wk )

Explanation of the Changes:

ALL(SALES[WEEK]): Removes the filter on the WEEK column so that all stores with sales in the YTD period are included in the calculation, regardless of the selected week.
Filters on Year and Week: Ensure the measure respects the selected year (SALES[YEAR] = yr) and calculates cumulative sales up to the selected week (SALES[WEEK] <= wk).

This adjustment ensures that the table displays all stores that have contributed to YTD sales, even if they are not active in the currently selected week. Let me know if further clarifications or refinements are needed!

Best regards,

Re: YTD/ LTM show correct total but row values missing

Anonymous — Mon, 09 Dec 2024 02:44:21 GMT

Thanks for the reply from DataNinja777 , please allow me to provide another insight:

Hi, Dr_BB

I believe the reason only one row of data is displayed is due to the interaction between the slicer and your visualisation object. The slicer's data comes from the original data, so it directly filters the original data rather than the expected result.

Here is my solution:

My sample data is as follows:

1.Firstly, create the following calculation table. Ensure that there is no relationship established between this calculation table and the original table:

slicert = DISTINCT(ALLEXCEPT('SALES','SALES'[STORE],'SALES'[SALES]))

2.Secondly, use the newly created calculation table as the slicer:

3.Next, create the following measure:

test_ytd = VAR yr = SELECTEDVALUE ( 'slicert'[YEAR] ) VAR mn = SELECTEDVALUE ( 'slicert'[MONTH] ) VAR wk = SELECTEDVALUE ( 'slicert'[WEEK] ) VAR sales_ytd = CALCULATE ( SUM ( 'SALES'[SALES] ), FILTER ( ALLEXCEPT ( 'SALES', 'SALES'[STORE] ), SALES[YEAR] = yr && SALES[WEEK] <= wk && SALES[MONTH] <= mn ) ) RETURN IF ( ISFILTERED ( 'slicert'[MONTH] ) && ISFILTERED ( 'slicert'[WEEK] ) && ISFILTERED ( 'slicert'[YEAR] ), sales_ytd, SUM ( 'SALES'[SALES] ) )

4.Here's my final result, which I hope meets your requirements.

5.Below are posts similar to your issue that might be helpful to you:

Solved: Total Revenue YTD for ALL years - Microsoft Fabric Community

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

Re: YTD/ LTM show correct total but row values missing

Dr_BB — Mon, 09 Dec 2024 13:03:54 GMT

DataNinja777
Thanks for the answer!

However, this solution is not working since the data is getting filtered because of the slicer interaction.

Thanks again

Re: YTD/ LTM show correct total but row values missing

Dr_BB — Mon, 09 Dec 2024 13:07:35 GMT

Hi Anonymous ,

Thanks a lot for your answer!

It is working for me. Just a small change in Step1 :

SALES_DIST_DATE = SUMMARIZE(SALES, (SALES[YEAR]), (SALES[MONTH]), (SALES[WEEK]))

I have more numeric columns in my fact table and more granularity columns as well. So the above code looked more efficient.

Note: Might reach out again in acse of any issue after end-to-end QC.

Thanks again!