Forum Discussion

wllmc's avatar
wllmc
New Member
7 years ago
Solved

Variables

Hi, I am looking for a way to add date as variables in the tables created. I have a few more other tables that are alike and want to avoid having to go into each table to update the dates.

 

ABC_SUMM_US_LY = CALCULATETABLE(SUMMARIZECOLUMNS(ABC_SUMM[SERV_NAME], "TTL CONT EX OH", SUM(ABC_SUMM[TTL CONT EX OH]), "TTL REV",SUM(ABC_SUMM[TTL REV])),ABC_SUMM[SERV_NAME]= "US", DATESBETWEEN(ABC_SUMM[MONTHYEAR],DATE(2017,1,1),DATE(2017,3,31)))

 

ABC_SUMM_US_TY = CALCULATETABLE(SUMMARIZECOLUMNS(ABC_SUMM[SERV_NAME], "TTL CONT EX OH", SUM(ABC_SUMM[TTL CONT EX OH]), "TTL REV",SUM(ABC_SUMM[TTL REV])),ABC_SUMM[SERV_NAME]= "US", DATESBETWEEN(ABC_SUMM[MONTHYEAR],DATE(2018,1,1),DATE(2018,3,31)))

 

I am not sure if I provided enough information as I am still relatively new to PowerBI.

 

Thank you

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    Try putting them into a measure then using the measure in the table creation maybe?

    Start Date = DATE(2017,1,1)
    End Date = DATE(2017,3,31)
    ABC_SUMM_US_LY =
CALCULATETABLE (
    SUMMARIZECOLUMNS (
        ABC_SUMM[SERV_NAME],
        "TTL CONT EX OH", SUM ( ABC_SUMM[TTL CONT EX OH] ),
        "TTL REV", SUM ( ABC_SUMM[TTL REV] )
    ),
    ABC_SUMM[SERV_NAME] = "US",
    DATESBETWEEN ( ABC_SUMM[MONTHYEAR], [Start Date], [End Date] )
)
    ABC_SUMM_US_TY =
CALCULATETABLE (
    SUMMARIZECOLUMNS (
        ABC_SUMM[SERV_NAME],
        "TTL CONT EX OH", SUM ( ABC_SUMM[TTL CONT EX OH] ),
        "TTL REV", SUM ( ABC_SUMM[TTL REV] )
    ),
    ABC_SUMM[SERV_NAME] = "US",
    DATESBETWEEN ( ABC_SUMM[MONTHYEAR], [Start Date], [End Date] )
)

10 Replies

  • Cmcmahan's avatar
    Cmcmahan
    Resident Rockstar
    7 years ago

    Are you trying to add a column of dates to your pre-existing table?  Or are you trying to create a large number of similar calculated tables?

     

    It seems from your example code that your problem is that you are making multiple calculated tables and don't want to copy/paste the same code for every period, changing the dates every time.  Depending on how you plan to use your data, you're VERY likely to not even need to create a calculated table for each date range.

     

    Questions:

    1) How are you displaying this data? If it's in a table/matrix, you can set up date ranges for each column, and then the table automatically sets up a filter context for you.  No need to create a million tables for each specific range you ever feel like messing with.

    2) How is a date grouping decided?  It looks like in the two examples you gave, you want Q1 2017 and Q1 2018 data.  PowerBI already comes with the ability to sort by quarter.   You can literally make a visual, drag the date into it as a value/axis/whatever's appropriate, click the dropdown to set it as a date hierarchy, and remove all the other parts of the hierarchy that aren't Quarter. Then drag your ABC_SUMM[TTL EX CONT EX OH] into the same visual, set it to aggregate as a sum, and let power bi do the rest!

    If you're looking for some other sort of date grouping, read up on creating a date dimension to add your own indicators!

    3) If you're trying to set up multiple visuals and have each one show a different quarter, you can set up visual level filters on each visual to only include specific date ranges.  No more messing about with extra tables!

     

     If none of these are a solution for your use case, please tell us more about it and we can figure out an answer better suited to you!

    • wllmc's avatar
      wllmc
      New Member
      7 years ago

      I am trying to re-created a report that was produced in excel which involved several aggregated calculations that lead me to create multiple calculated tables. My end goal is to show the City, Total Revenue and YoY change on a table/matrix and chart hence the tables for "this year" and "last year". I probably don't think its the ideal but I am open for a better method.

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    Try putting them into a measure then using the measure in the table creation maybe?

    Start Date = DATE(2017,1,1)
    End Date = DATE(2017,3,31)
    ABC_SUMM_US_LY =
CALCULATETABLE (
    SUMMARIZECOLUMNS (
        ABC_SUMM[SERV_NAME],
        "TTL CONT EX OH", SUM ( ABC_SUMM[TTL CONT EX OH] ),
        "TTL REV", SUM ( ABC_SUMM[TTL REV] )
    ),
    ABC_SUMM[SERV_NAME] = "US",
    DATESBETWEEN ( ABC_SUMM[MONTHYEAR], [Start Date], [End Date] )
)
    ABC_SUMM_US_TY =
CALCULATETABLE (
    SUMMARIZECOLUMNS (
        ABC_SUMM[SERV_NAME],
        "TTL CONT EX OH", SUM ( ABC_SUMM[TTL CONT EX OH] ),
        "TTL REV", SUM ( ABC_SUMM[TTL REV] )
    ),
    ABC_SUMM[SERV_NAME] = "US",
    DATESBETWEEN ( ABC_SUMM[MONTHYEAR], [Start Date], [End Date] )
)
    • Cmcmahan's avatar
      Cmcmahan
      Resident Rockstar
      7 years ago

      jdbuchanan71 But if he goes through and ever does a data refresh, all his calculated tables will re-calculate with whatever date happens to be in the measure at that time. And he's still going in to manually edit 2 measures every time he wants to create a table.  It would honestly be a better solution to copy/paste and manually edit each date range in the table's DAX.

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        7 years ago

        Cmcmahan He had said he was creating several tables using the same dates.  Puting them into a measure lets him change the date in one place and it flows to all his calculated tables which is what I thought he was looking to do.  If he is wanting different dates for different tables then my solution dosn't work.

    • wllmc's avatar
      wllmc
      New Member
      7 years ago

      Thank you, this works for what I need.