Interesting conundrum using the Day function when reporting monthly

[Oivind]

Hi guys!

I am trying to figure out how to get a "monthly" report of my data. I have data-values from date 1-17 as shown below. I’ve created the following measure:

Please Note:

OurDataset: The dataset we use

OurColumn1: Count

OurColumn2: Date

Total = CALCULATE(COUNTA('OurDataset'[OurColumn1]);FILTER(ALLSELECTED('OurDataset');'OurDataset'[OurColumn2].[Day] <=MAX('OurDataset'[OurColumn2].[Day])&&'OurDataset'[OurColumn2].[Day]<=(TODAY())))

One issue with using the "Day" function is that it also returns values for the other dates as well (18th to 31st).

The conundrum:

How do I create a report that shows the entire month, but only present the data I have, ie. 1-17.

The "Day" function will always return data in Gregorian format, and I'm fine with that, but why doesn’t the line stop at the 17th?

Any feedback on the matter will be much appreciated.

[Anonymous · Accepted solution]

Hi @Oivind,

I test the scenario as yours and get the same issue, when I add .[Day] after Date column, it presents other dates(18th-31st).  I suspect the issue is caused by the .[Day] function, unfortunately, I am not able to find any official articles stating the usage of this function.

In your scenario, you can remove  .[Day] from the formula. I test it and everything works as expected, you can check the following  screenshot and this attached PBIX file.

Total = CALCULATE(COUNTA('OurDataset'[OurColumn1]);FILTER(ALLSELECTED('OurDataset');'OurDataset'[OurColumn2]<=MAX('OurDataset'[OurColumn2])&&'OurDataset'[OurColumn2]<=(TODAY())))


Thanks,
Lydia Zhang

[Anonymous · Accepted solution]

Hi @Oivind,

I test the scenario as yours and get the same issue, when I add .[Day] after Date column, it presents other dates(18th-31st).  I suspect the issue is caused by the .[Day] function, unfortunately, I am not able to find any official articles stating the usage of this function.

In your scenario, you can remove  .[Day] from the formula. I test it and everything works as expected, you can check the following  screenshot and this attached PBIX file.

Total = CALCULATE(COUNTA('OurDataset'[OurColumn1]);FILTER(ALLSELECTED('OurDataset');'OurDataset'[OurColumn2]<=MAX('OurDataset'[OurColumn2])&&'OurDataset'[OurColumn2]<=(TODAY())))


Thanks,
Lydia Zhang

[Oivind · Accepted solution]

Hello @Anonymous,

I thought I had tried everything but sometimes the solution is much more simpler than what you expect!

Thank you very much for contribution! 

Kind regards,

Øyvind

[Anonymous]

Hi @Oivind,

Glad to hear that the issue is resolved. We will appreciate it if you can accept helpful reply as solution, that way, other community members will easily find the solution when they get same issues.

Thanks,
Lydia Zhang

[Oivind]

Hello again, @Anonymous,

I had to add .[date] to the formula to count today's date as well. So the formula modified looks like this:

Now it works perfect!

Total = CALCULATE(COUNTA('OurDataset'[OurColumn1]);FILTER(ALLSELECTED('OurDataset');'OurDataset'[OurColumn2]<=MAX('OurDataset'[OurColumn2])&&'OurDataset'[OurColumn2].[Date]<=(TODAY())))

Regards,

Øyvind