Forum Discussion

Oivind's avatar
Oivind
Regular Visitor
9 years ago
Solved

Interesting conundrum using the Day function when reporting monthly

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 d...
  • Anonymous's avatar
    Anonymous
    9 years ago

    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's avatar
    Oivind
    9 years ago

    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's avatar
Anonymous
Not applicable
9 years ago

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's avatar
Oivind
Regular Visitor
9 years ago

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's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      Oivind
      Regular Visitor
      9 years ago

      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