Sum of a column (measures) issue

[gator99]

Sorry, couldn't find this by searching but I was curious, I have created several measures to caculate Month to Date numbers as well as Previous year MTD.

MTD Days Open = TOTALMTD(COUNT(vwBusinessSummary[FromDate]),'vwBusinessSummary'[FromDate])

PYMTD DAYS OPEN = CALCULATE([MTD Days Open],DATEADD(DATESMTD(AllDates[TheDate]),-1,YEAR))

attached is a screen shot of my data, as you can see the MTD is summing but the PYMTD is not.  Any suggestions?

 

[Greg_Deckler]

Without seeing your data, or a sample of it, and replicating the issue, my guess would be that something is making the measure formula essentially invalid in the "ALL" context, which is essentially what the total line represents.

[gator99]

See attached for the data.  

Additional Information, this is the dates open for our stores, Monday through Saturday (no Sunday), I am wondering if not having continuous days an issue, though it works for the MTD.

FromDate

[Sean]

@gator99

How are the 2 tables related?

What do you get with this formula?

PYMTD DAYS OPEN =
CALCULATE (
    COUNT ( vwBusinessSummary[FromDate] ),
    DATEADD ( DATESMTD ( AllDates[TheDate] ), -1, YEAR )
)

DATEADD does require a contiguous date column - but it seems you already have a date table (AllDates)

You can also try

PYMTD DAYS OPEN 2 =
CALCULATE (
    COUNT ( vwBusinessSummary[FromDate] ),
    SAMEPERIODLASTYEAR ( AllDates[TheDate] )
)

[gator99]

First Formula gives me same results but no sum for the column, second give me the error rearding contiguous dates.

Relationship (see attached).

thanks for all your assistance.

[v-huizhn-msft]

Hi @gator99,

Please use the date table in both formulas, and check if it works.

MTD Days Open = TOTALMTD(COUNT(vwBusinessSummary[FromDate]),AllDates[TheDate])
PYMTD DAYS OPEN = CALCULATE([MTD Days Open],DATEADD(DATESMTD(AllDates[TheDate]),-1,YEAR))

If you have other issues, please let me know.

Best Regards,
Angelia

[gator99]

When I changed the MTD Days Open to what you suggested I loose the summation on the MTD Days Open colum as well.

[v-huizhn-msft]

Hi @gator99,

Do you have resolve your issue? If it does, I am very gald to hear you have resolve your issue, please mark the corresponding reply as answer if it's helpful, so other people will find solution easily. If it doesn't, please feel free to ask.

Best Regards,
Angelia

[gator99]

No it didn't resolve my issue.

[gator99]

Any other thoughts on my issue?

[JanMulkens]

Hi @gator99,

While talking about this problem with @RobJo and @edejes2 we came to the conclusion that we won't be able to fully help you unless you can share the pbix file (obviously with dummy data) or dummy data itself in XLSX or CSV format for example.

The new official Microsoft Power BI User Group board has a nice place to share files, or if you prefer, you can upload to any other place of your choice.

We discussed this issue in the "Global Power BI - Forum Aid" event.

Come join us at the new pbiusergroup.com board for more info & help

[gator99]

EDIT ***DELETED LAST POST****

So I think I have what you need, please find attached a zip file with the pbix and two excel files that builds my report.

data/pbix

[gator99]

Just thought I would reach out again to see if what I shared was accessible? Also to see if anyone had any suggestions.  Thanks again.

[gator99]

Sorry to bring this back up but I still have had no luck trying to figure out why one column sums and the other does not.  Any other thoughts?

Thank you so much for any help.

JBG