Trying to create a comparison of the running total of last year

[RossS]

I have a running total for the current month. I am trying to create a running total for this month last year, then compare it with the current month's running total.

For this month's running total I have the following:

Running Total = CALCULATE(SUM(Sales[Sales Amount Actual]),

FILTER(ALLSELECTED(Sales),

Sales[Sales Amount Actual] <=MAX(Sales[Posting Date])),

MONTH(Sales[Posting Date]) = MONTH(TODAY()),

YEAR(Sales[Posting Date]) = YEAR(TODAY()))

The comparrison won't be for this date (i.e. it is the 15th of the month today, but it won't neccesarily be the 15th of this month last year). The comparrison will be for the working day (i.e. today is the 11th working day of the month and this needs to be compared with the 11th working day of this month last year).

[v-tsaipranay]

Hi @RossS ,

Glad to hear the working-day based MTD comparison is working for you. To extend this further:

1. Adding 2023 (or any specific year):
   Instead of using LASTYEAR -1, which shifts dates by calendar logic, you can adapt the same working-day running total pattern and simply fix the year filter. For example:

RT MTD 2023 (by WD) :=
VAR tgtYear = 2023
VAR tgtMonth = MONTH ( TODAY() )
VAR wdAxis   = MAX ( 'DimDate'[WorkingDayOfMonth] )
VAR lastWDwithData =
    CALCULATE (
        MAX ( 'DimDate'[WorkingDayOfMonth] ),
        ALL ( 'DimDate' ),
        'DimDate'[Year] = tgtYear,
        'DimDate'[MonthNumber] = tgtMonth,
        'DimDate'[IsWorkingDay] = 1,
        NOT ISBLANK ( CALCULATE ( [Sales Amount] ) )
    )
RETURN
IF (
    ISBLANK ( lastWDwithData ) || wdAxis > lastWDwithData,
    BLANK(),
    CALCULATE (
        [Sales Amount],
        KEEPFILTERS ( 'DimDate'[Year] = tgtYear ),
        KEEPFILTERS ( 'DimDate'[MonthNumber] = tgtMonth ),
        KEEPFILTERS ( 'DimDate'[WorkingDayOfMonth] <= wdAxis )
    )
)

This way you can create one measure each for CY, LY, and 2023, all using the same working-day rank logic.

2. Stopping the line at the last data point:
   The key is to let the measure return BLANK() once you pass the last working day that has actual data. In the code above, the lastWDwithData variable checks the maximum working day with data for that year/month. Anything beyond that will return BLANK, so your line will stop instead of continuing flat.
3. Visual setting:
   Make sure “Show items with no data” is turned off on the X-axis field. Together with the BLANK() logic, the chart will now stop at the true last point of data.

ho[pe this helps.

Thank you.

[v-tsaipranay · Accepted solution]

Hi @RossS ,

I used some sample data on my end and implemented it. Please review the attached PBIX file and let me know if this resolves your issue.

Thank you for using Microsoft Community Forum.

[RossS]

Thank you for this, I can accept it as a solution as I have implemented it and it works.

I would also like to compare the running totals to 2023. I have tried to use LASTYEAR -1 but it doesn't give the desired outcome.

Also, when there is no data in the graph, I would like the line to stop. At the moment, the line continues horizontally. Is there a way to get it to stop at the last data point? 

[v-tsaipranay]

Hi @RossS ,

Since sharing the information as an image might not be suitable or could lead to inaccurate results, I kindly request that you provide the dummy data along with the expected output.

Please follow the below link to share the date : How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.

[RossS]

I've read through it, it doesn't explain anything about making the data anonymous. How do I make 2yrs worth of data anonymous? 

[RossS]

I am struggling to make the data anonymous.

I have a sales table with each line as an invoice. I can delete the colum with our customer's names, but there is a column with the invoiced amount that I do not wish to share. Is there a way I can randomise this data?

"show the data model in a picture"

Is this simply a screenshot from the Model View?

[v-tsaipranay]

Hi @RossS ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[v-tsaipranay]

Hi @RossS ,

It's been a while since we last heard from you. We are ready to assist you with resolving the issue, but we need the necessary details from you. Kindly share the information required so we can better understand and address your issue.

Thank you.

[v-tsaipranay]

Hi @RossS ,

We haven't heard from you in a while. We're ready to help resolve your issue, but we need more details from you. Please provide the necessary information so we can better assist you.

Thank you.

[govind_021]

Hi @RossS 
Could you please share the structure of your data model and a screenshot of the visual where you're displaying the running total comparison?

[v-pagayam-msft]

Hi @RossS ,
Thank you for the prompt response @FBergamaschi !
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Hope this helps !
Thank you.

Regards,
Pallavi.

[FBergamaschi]

A first note, your DAX code looks complex and dangerous (are you sure you need ALLSELECTED? For what purpose?)

That said, to help you, I need

1 - to know how do you define a day to be working or not

2 - to see some data for two years and your data model (I do not see in the DAX code the Calendar table and this is surely needed)

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here