Forum Discussion

Natrify's avatar
Natrify
Frequent Visitor
1 year ago
Solved

Help needed for Current month and previous month graph

Hi all, 

 

I'm in a strange predicament, I'm trying to build out a current month and previous month comparison Dashboard. The idea is to use one month-year slicer that effects all visuals. e.g. if you select Jan 2025, half the visuals filter to show data in that period, the other half filter to show data in Dec 2024. For this example I'll use one measure:

I have two main tables:

EmployeePresence

data similar to the following:


Dim_date

 

The two tables are linked together by date, and my main measure to calculate joiners is the following:

Joiners = COUNTROWS(FILTER(EmployeePresenceByDate, EmployeePresenceByDate[J&L] = 1))


A simple measure that counts rows where people have joined. Below is the kind of visual set up that I'm trying to make:

 

Now I figured out how to work with card visuals to get last months joiners to show on a card, but it doesn't work as well in a linechart because the line chart coniders each day, where the card can just aggregate the total for last month.

 

Help is much Appreciated, Thanks.





 

Need Help
  • ullassl1991's avatar
    ullassl1991
    1 year ago

    Hi Natrify 
    To solve this you can create two measures for joiners current and previous month but it won't work at the end of the year, there is workaround,
    Step 1; Duplicate the Dim_date table and say name it new_dim_date, create a relationship between Dim_date and new_dim_date on date column

    Step 2: 
    Create the measure which will provide joiners value for last month with the selection of slicer

    Last_month_J&L = 
var _maxdate= EOMONTH(MAX(Dim_date[Date]),-1)
var _last_month=
DATESINPERIOD(New_Dim_date[Date],_maxdate,-1,MONTH)

RETURN 
CALCULATE([#J&L],REMOVEFILTERS(Dim_date),KEEPFILTERS(_last_month),USERELATIONSHIP(Dim_date[Date],New_Dim_date[Date]))

     Step 3:
    In one visual use Last_month_J&L measue with month-year from New_Dim_date table and another visual with #J&L measure with month-year from Dim_date

    I guess this will solve your problem let me know if its resolved


5 Replies

  • v-dineshya's avatar
    v-dineshya
    Community Support
    1 year ago

    Hi Natrify,

    Thank you for reaching out to the Microsoft Community Forum.

     

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    Please show the expected outcome based on the sample data you provided.

     

     

    If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

     

    Thank you

  • v-dineshya's avatar
    v-dineshya
    Community Support
    1 year ago

    Hi Natrify ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    Please show the expected outcome based on the sample data you provided.

     

     

    If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

     

    Thank you

    • v-dineshya's avatar
      v-dineshya
      Community Support
      1 year ago

      Hi Natrify ,

      Thank you for reaching out to the Microsoft Community Forum.

       

      Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
      Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
      Please show the expected outcome based on the sample data you provided.

       

       

      If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

       

      Thank you

      • v-dineshya's avatar
        v-dineshya
        Community Support
        1 year ago

        Hi Natrify ,

        Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
        Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
        Please show the expected outcome based on the sample data you provided.

         

         

        If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

         

        Thank you

  • ullassl1991's avatar
    ullassl1991
    Helper I
    1 year ago

    Hi Natrify 
    To solve this you can create two measures for joiners current and previous month but it won't work at the end of the year, there is workaround,
    Step 1; Duplicate the Dim_date table and say name it new_dim_date, create a relationship between Dim_date and new_dim_date on date column

    Step 2: 
    Create the measure which will provide joiners value for last month with the selection of slicer

    Last_month_J&L = 
var _maxdate= EOMONTH(MAX(Dim_date[Date]),-1)
var _last_month=
DATESINPERIOD(New_Dim_date[Date],_maxdate,-1,MONTH)

RETURN 
CALCULATE([#J&L],REMOVEFILTERS(Dim_date),KEEPFILTERS(_last_month),USERELATIONSHIP(Dim_date[Date],New_Dim_date[Date]))

     Step 3:
    In one visual use Last_month_J&L measue with month-year from New_Dim_date table and another visual with #J&L measure with month-year from Dim_date

    I guess this will solve your problem let me know if its resolved