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 o...
  • Anonymous's avatar
    Anonymous
    2 years 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.

Anonymous's avatar
Anonymous
Not applicable
2 years 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
    2 years 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
      2 years 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.

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

        Ah, sure... 🙈

        Thank you so much, it's working fine! 👍

         

        Kind regards,
        Michael