Need help in calculating the Prior and Current YTD dynamic

[MaliniBaktha]

Hi, 

I have a dataset that has year, year_month, region, specialty, count(numbers). I want the data set set to show currentYTD(with the latest month)  when they select current year they see all data from months of the previous and current month  of the current year and for Prior YTD they have to see prior year same time period  , but I am not able to achieve it for some reason. I have tried 

MTD = TOTALMTD(SUM('OLD ONE'[COUNT(DISTINCT APT)]),'OLD ONE'[MONTH]) - to get numbers by adding in the subsequent month but it still shows the currennt month number and not the running total of the month . Additional issue, I am seeing is my year calculation is not from jan-dec it is from July to June 

I followed this blog Total Month to Date(TOTALMTD) DAX function in Power Bi (powerbidocs.com) and some other blogs

Is there a better way to do this ? can anybody help in the logic I am missing 

[v-xuxinyi-msft · Accepted solution]

Hi @MaliniBaktha 

Thanks for the reply from @lbendlin .

Maybe you can try SAMEPERIODLASTYEAR.

Here is the test I did by creating a simple sample data for your reference.

1. Create a calculated table as the slicer

Date = VALUES('Table'[Year_Month])

2. Create two measures to calculate the values of last year's and this year's values.

current = CALCULATE(SUM('Table'[Value]), FILTER('Table', [Year_Month] >= MIN('Date'[Year_Month]) && [Year_Month] <= MAX('Date'[Year_Month])))

previous = CALCULATE(SUM('Table'[Value]), FILTER('Table', [Year_Month] IN SAMEPERIODLASTYEAR('Date'[Year_Month])))

Output:

If this doesn't help you, then as @lbendlin said, please provide some sample data and the expected results based on the sample data. So that we can better help you.

Best Regards,
Yulia Xu

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

[v-xuxinyi-msft · Accepted solution]

Hi @MaliniBaktha 

Thanks for the reply from @lbendlin .

Maybe you can try SAMEPERIODLASTYEAR.

Here is the test I did by creating a simple sample data for your reference.

1. Create a calculated table as the slicer

Date = VALUES('Table'[Year_Month])

2. Create two measures to calculate the values of last year's and this year's values.

current = CALCULATE(SUM('Table'[Value]), FILTER('Table', [Year_Month] >= MIN('Date'[Year_Month]) && [Year_Month] <= MAX('Date'[Year_Month])))

previous = CALCULATE(SUM('Table'[Value]), FILTER('Table', [Year_Month] IN SAMEPERIODLASTYEAR('Date'[Year_Month])))

Output:

If this doesn't help you, then as @lbendlin said, please provide some sample data and the expected results based on the sample data. So that we can better help you.

Best Regards,
Yulia Xu

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[MaliniBaktha]

yeah , sorry here is the link https://docs.google.com/spreadsheets/d/19b8xeS8BNkKXOwhx81NJFWLjvBBnFAjr/edit?usp=sharing&ouid=11312... . so the present month is july and if I check next month I need the cummulative addtion of july+august of 2024 for CYTD and for Prior Year to date I need july + august 2024

[lbendlin]

Is Log_Year your Fiscal Year ?  Do your fiscal years start in July?

[MaliniBaktha]

@Ibendlin Sorry for the late reply, had some emergency travel to do. so yes the fiscal year starts in July 

[lbendlin · Accepted solution]

Use a static calendar table that has the details for your fiscal periods. Use SAMEPERIODLASTYEAR for the yoy comparisons and DATEADD (x,-3,MONTH) for the qoq formulas.