Forum Discussion

jdubs's avatar
jdubs
Helper V
6 years ago

Create new calculated table by date range

How do I create a new calculated table and select columns from an existing table while also filtering that data by a date range.

 

Basically I want to create 2 tables of records created in the last 6 months and then created in the same 6 month range but a year ago.

 

Ultimately I will want to display this as a clustered column chart displaying the count of these records 'This Year vs. Last Year'

 

 

Need Help

4 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, jdubs 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    If you want to create two calculated columns, you may try the following dax.

    Table 1 = 
FILTER(
    'Table',
    YEAR('Table'[Date])=YEAR(TODAY())&&
    MONTH('Table'[Date])<MONTH(TODAY())&&
    MONTH('Table'[Date])>=MONTH(TODAY())-6
)

Table 2 = 
FILTER(
    'Table',
    YEAR('Table'[Date])=YEAR(TODAY())-1&&
    MONTH('Table'[Date])<MONTH(TODAY())&&
    MONTH('Table'[Date])>=MONTH(TODAY())-6
)

     

    If you want to create two measures, you may try dax as below.

    Count This Year = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        YEAR('Table'[Date])=YEAR(TODAY())&&
        MONTH('Table'[Date])<MONTH(TODAY())&&
        MONTH('Table'[Date])>=MONTH(TODAY())-6
    )
)

Count Last Year = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        YEAR('Table'[Date])=YEAR(TODAY())-1&&
        MONTH('Table'[Date])<MONTH(TODAY())&&
        MONTH('Table'[Date])>=MONTH(TODAY())-6
    )
)

     

    Result:

     

    Best Regards

    Allan

     

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

    • jdubs's avatar
      jdubs
      Helper V
      6 years ago

      The is perfect Allan. The only issue is that it gives me to the beginning of February when I need it to stop at Today's date (-6 months) e.g. Feb. 20th. if today is Aug. 20th. 

       

      I'm going to do a little research. Let me know if you have any ideas.

       

      Thanks!

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    jdubs , you can create table using calculatetable

    https://docs.microsoft.com/en-us/dax/calculatetable-function-dax

     

    But for This Year Vs Last Year you can use time intelligence with Date

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

     

     

    Power BI — YTD
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

    • jdubs's avatar
      jdubs
      Helper V
      6 years ago

      Hi,

       

      Thank you but what if I want a count of records and not a sum of sales? Also, how would I use CALCULATETABLE to create a table of 'Last 6 Months' and then 'Last 6 Months (Previous Year)'?

       

      Thanks.