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_SU...
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] )
)
Cmcmahan's avatar
Cmcmahan
Icon for Resident Rockstar rankResident 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
Icon for Super User rankSuper 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.

  • Cmcmahan's avatar
    Cmcmahan
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Ahh, see I read that as he has multiple tables and the only thing he wants to change between tables is the date range.  If he's using the same date multiple times, then maybe set it up as a measure, but I'm still steadfastly against creating a ton of random tables when you can just aggregate on the fly and in context with your visuals.

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

      If we are just looking for an easier way to create the tables one at a time putting the dates as variables in the front is a bit better.

       

      ABC_SUMM_US_LY =
VAR StartDate = DATE ( 2017, 1, 1 )
VAR ENdDate = DATE ( 2017, 3, 31 )
RETURN
    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], StartDate, EndDate )
    )
    • jdbuchanan71's avatar
      jdbuchanan71
      Icon for Super User rankSuper User
      7 years ago

      Cmcmahan I agree it is far better to figure out how to set up the model and write the measures properly so you don't need all the extra tables.  We end up with enough tables from TopN, Groupings, Switches for display measure, etc, etc.  :smileyhappy: