Forum Discussion

cn4422's avatar
cn4422
Icon for Helper V rankHelper V
2 years ago
Solved

Calculate+Datesbeween in Line Graph

Hi,

 

I have created the following measure, which I then implemented in a line graph. However, the measuer is only showing the total sum for every month, and not the real sum/month.

 

Any ideas on how to fix this?

 

Count Forms Category Test 2023 =

CALCULATE(COUNT('lead'[Forms category]),

ALL(),

'lead'[Country]="Test Form",

'lead'[Forms category]="Test Category",

DATESBETWEEN(Datum[Date],DATE(2023,1,1),DATE(2023,8,31)

))

 

 

 

When I'm not using "time" in my measure but a slicer, it's working fine (but I want to it work without date slicer).

 

 

The actual goal is to display two graphs, one for 2023 and one for 2024 in the same 12 months chart.

 

Thanks for your help!

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi cn4422 

     

    Try using VALUES(Datum[Date]. [Month]) in your measure to ensure that the data is summarized by month.

    Count Forms Category Test 2023 by Month = 
CALCULATE(
    COUNT('lead'[Forms category]),
    'lead'[Country] = "Test Form",
    'lead'[Forms category] = "Test Category",
    DATESBETWEEN(Datum[Date], DATE(2023, 1, 1), DATE(2023, 12, 31)),
    VALUES(Datum[Date].[Month])
)

     

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi cn4422 

     

    Try using VALUES(Datum[Date]. [Month]) in your measure to ensure that the data is summarized by month.

    Count Forms Category Test 2023 by Month = 
CALCULATE(
    COUNT('lead'[Forms category]),
    'lead'[Country] = "Test Form",
    'lead'[Forms category] = "Test Category",
    DATESBETWEEN(Datum[Date], DATE(2023, 1, 1), DATE(2023, 12, 31)),
    VALUES(Datum[Date].[Month])
)

     

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

    • cn4422's avatar
      cn4422
      Icon for Helper V rankHelper V
      1 year ago

      Thank you very much, that did the trick! ðŸ¤—

       

      One more question, if I may:

       

      With your help I've created this measure for 2024:

      Count Forms Category DE 2024 TEst 2 plus date slicer =
      CALCULATE(COUNT('lead'[Forms category]),
      'lead'[Country]="Test Country",
      'lead'[Forms category]="Test Form",
          VALUES(Datum[Date].[Monat]
      ))
       
      This works just fine in the graph.
       
      Then I added this measure for SPLY 2023:
       
      Count Forms Category DE 2023 SPLY TEst 2 plus date slicer =
      VAR dateRange = DATESBETWEEN('Datum'[Date], MIN(Datum[Date]),MAX(Datum[Date]))
      Var dateRange2 = Sameperiodlastyear(dateRange)
      RETURN
      CALCULATE(COUNT('lead'[Forms category]),
      'lead'[Country]="Test Country",
      'lead'[Forms category]="Test form",
          ALL('Datum'),
      dateRange2
      )
       
      Upon adding this measure to the graph, it shows all 12 months (from 2023) which I don't want --> It should only show the months as selected in the slicer.
       
      Maybe the error lies somewhere in this line:
      DATESBETWEEN('Datum'[Date], MIN(Datum[Date]),MAX(Datum[Date]))
      ...
       
      Thanks again for your help!
       
       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi cn4422 

         

        The problem you are experiencing is due to the use of the ALL('Datum') function, which ignores all filters in the Datum table. Refer to link: ALL function (DAX) - DAX | Microsoft Learn.

        Drop ALL(Datum) and try the following DAX:

        Count Forms Category DE 2023 SPLY TEst 2 plus date slicer = 
CALCULATE(
    COUNT('lead'[Forms category]),
    'lead'[Country] = "Test Form",
    'lead'[Forms category] = "Test Category",
    SAMEPERIODLASTYEAR(Datum[Date])
)

         

         

         

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