Forum Discussion

damit23183's avatar
damit23183
Microsoft Employee
5 years ago
Solved

Difference between two months based on Month's SLICER

Hi,

 

I am working on to get result between two months based on slicer selection.

 

For Example; I have this table where you can see data for May month only.

 

What I want is when i select TIMELINE slicer for April and May, I should see only 3000 as difference because there is no data for specific URL for APRIL month. 

 

 

I am trying but getting difference 0 which is wrong as i dont have any data related to GAME in april so it should be 3000-0 = 3000.

 

This is the formula I am using,

 

Result = 

 

var maxdate = max(table[date])

var mindate = min(table[date])

 

var maxcounts = Calculate ( distinctcount(URL), Table[Date] = maxdate)

var mincounts = Calculate ( distinctcount(URL), Table[Date] = mindate)

 

return 

maxcounts - mincounts.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi damit23183 ,

     

    According to my understanding, you want to calaculate the differences(count of URL) between the two selected months, right?

    Please try this:

    Measure = 
var _min=MIN('Table'[Date].[MonthNo])
var _max=MAX('Table'[Date].[MonthNo])
return CALCULATE(DISTINCTCOUNT('Table'[URL]),FILTER('Table',MONTH([Date])=_max)) - CALCULATE(DISTINCTCOUNT('Table'[URL]),FILTER('Table',MONTH([Date])=_min))

    The final output is shown below:

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

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi damit23183 ,

     

    You need to use ALL function to pick up the values and then use the dates to filter the information.

     

    How are you making the selection of the dates is it based on a calendar table? Is the tabel related or disconnected?

     

    Can you share more information about your model setup and your visualizations?

     

    • damit23183's avatar
      damit23183
      Microsoft Employee
      5 years ago

      Hi,

      Thanks for your response.

       

      Yes I am using calendar table from where DATE column is used as a slicer (TimeLine Slicer) with Month and Year.

      Calendar table is related (Mapped in datamodel) to fact table based on Dates columns from both table.

      On Visulization,

      I have one timeline slicer and one table thats all (I have attached screenshot of visulization in main question).

       

      Requirement scenario;

       

      Availalbe information, i have one categorial data available only in APRIL month 2021 which means it does not exists in any other month of 2021 year till now. 

       

      What I am looking for:

       

      When i select month range from January to April I should see max value from April and Min value for January that you see in screen shot above. Once selection happened, on result column DIFFERENCE should be April Number - January Number.

      However, on other hand when i select April to May range I should see May Number - APril Number (Considering May is MAX month and April is Min month). Therefore, difference should be in - (Minus) becuase May has 0 records.

      On other scenario, if i select January to May where both months do not have any data so it should 0-0 = 0 difference.

       

      Hope this will help you to understand what exactly I am trying to achieve here. In short, i would like to set up dynamic slicer with which I can control all data.

       

      Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi damit23183 ,

     

    According to my understanding, you want to calaculate the differences(count of URL) between the two selected months, right?

    Please try this:

    Measure = 
var _min=MIN('Table'[Date].[MonthNo])
var _max=MAX('Table'[Date].[MonthNo])
return CALCULATE(DISTINCTCOUNT('Table'[URL]),FILTER('Table',MONTH([Date])=_max)) - CALCULATE(DISTINCTCOUNT('Table'[URL]),FILTER('Table',MONTH([Date])=_min))

    The final output is shown below:

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