Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Display two visualizations in one dashboard with two different time ranges

Hi all,

 

maybe it is a flat question, but my colleague and me we are struggling by displaying two seperate visualizations in order to comapre two different time periods in one dashboard. That means that we want to create two bar charts, where

 

  • the first bar chart shows the time period from -60 days until -30 days
  • the second bar chart shows -30 days until today (TODAY() )

 

We therefore used several approaches

1. (Based on the following Link), we created two distinct tables that provide us the dates needed by using the following formulas

 

Minus60tillMinus30Days = CALENDAR(TODAY()-60,TODAY()-30)

 

 

Minus30tillToday = CALENDAR(TODAY()-30,TODAY())

 

So far so good. We had two wonderful tables as an output with the needed days in that time period. The next step was to edit the relation between them. We set one-to-many and saved it. Subsequently, we wanted to display our target attribute in the time periode from Minus60tillMinus30Days... and here the story ends because Microsoft Power BI says  that there are duplicates in the database even though we removed all duplicates before.

 

2. Second, we tried to reconstruct the time periods by using a slicer according to this link. Unfortunately, we neither had success.

 

Before using Microsoft Power BI, we used Tableau and there we were able to create a parameter with a specific anchor day and then using a calculated field, as follows:

 

IF DATEDIFF('day',[Datum],[Today / Selected Latest Day]) < 60 AND DATEDIFF('day',[Datum],[Today / Selected Latest Day]) > 30 THEN
    [Datum]
END

Is there a light-weight solution to re-create this formula in Power BI?

 

We are looking forward to answers and hope that we clearly explained what our issue is.

Thanks and best regards,

Conny & Vaishali

Need Help
Tips and Tricks

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    Anonymous you dont need to creat two seperate date tables, have one date table and link with your transaction table and use measure to calculate the value. In this case you can use datesbetween function to calculate resepective value. There are lot of resources available online which can explain on how this function works.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi parry2k,

       

      thanks for the quick reply!  Yes, that sounds good. We already tried to use the DATESBETWEEN() formula, but what do you mean by "have one data table and link with your transaction table" exactly?

       

      we tried to use the following steps:

       

      1. One date table and link it with the transaction table

      We do not exactly know what is meant by this statement. Do you mean duplicating the initial data table with the following formula:

      TableNew = ALL('OldTable')

      OR

      should we create a new Table that automatically calculates the -60 days in the past because anyhow the -30 are included in the -60 by using the following formula:

      DateNew-60 = CALENDAR(TODAY()-60,TODAY())

      OR

      anything completely different?

      2. Creating a measure with the following formula

      DaysBetween-60-30 = CALCULATE(AVERAGE('TargetAttribute')), DATESBETWEEN('TableNew[Date]',
TODAY()-60,
TODAY()-30)
))

      3. Creating a measure with the following formula

      DaysBetween-30TODAY = CALCULATE(AVERAGE('TargetAttribute')), DATESBETWEEN('TableNew[Date]',
TODAY()-30,
TODAY())
))

      4. Plot these data.

      parry2k: Do you know why Power BI says that we have duplicates in our data after performing these steps? In which step are data duplicated and how can we avoid the duplication?


      Thanks and best regards

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        7 years ago

        Anonymous sorry there was typo, I was saying one DATE table and set relationship with your transaction table. Yes, you should measure using datesbetween.

         

        Not sure what is the duplicate? I think it will be easier if you put together sample pbix file and share here so that we can look into it.