Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Need help to create Dax Measure

  • 1.  Need help to create Dax Measure

    Silver Contributor
    Posted Mar 29, 2020 10:53 AM
    Hi Friends,

    My requirement is to create Quarter filter , For Example if i click Q1 and 2018  it has to
    show data for 2018-Q1, 2017-Q4, 2017-Q3,2017-Q2, Please help me how to get done with this.
    Please find the attached Sample files.
    Thanks in Advance



    ------------------------------
    Mohamed Azarudeen
    BI Developer
    IV
    chennai
    9790777218
    ------------------------------

    Attachment(s)

    pbix
    TestpowerBI.pbix   32K 1 version
    xlsx
    test.xlsx   8K 1 version
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Need help to create Dax Measure

    Gold Contributor
    Posted Mar 29, 2020 02:48 PM
    Hi Mohamed,

    First, you need to create a separate date table using the CALENDARAUTO function (more info there : https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/).

    Then you will create a relation beetween the date table and the source table.

    You'll then be able to create a measure to perform a cumulative sum of the sales in your visual :
    CALCULATE(SUMX(Sheet1,Sheet1[Sales]), FILTER(Date,Date[Date]<MAX(Date[Date]))

    ------------------------------
    Vincent L.
    Chartered accountant - Expert-comptable
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Need help to create Dax Measure

    Silver Contributor
    Posted Apr 01, 2020 11:06 PM
    Hi , Vincent L. Thank you very For you reply, i had already have a Data table in original data Model,
    Following measure i have tried ,Please refer below , only thing i have stuck in calculate,
    selected value function select only one quarter , rest of three quarter should be come in calculate table
    Please find the below query and try to fine tune  the query .

    This is my scenario
    for example If I clicked 2018- 1 , i wants to show a data for sales
    2017-4,2017-3,2017-2.  Its should work dynamically .Please find the below screen Shot and PBIx files. here i have used multiple filter ,
    without clicked multiple filter in a single click its should work for Last 3 quarter Sales.

    This is a measure i have tried

    Test8 =

    ---------------------------------------------------------

    var selQtr = SELECTEDVALUE('Sheet1 (2)'[Year and Qtr])

    VAR m =

    SELECTEDVALUE ( 'Sheet1 (2)'[DATE] )

    VAR O =

    FIRSTDATE ( DATEADD ( 'Sheet1 (2)'[DATE], -4, QUARTER ) )

     

    ---------------------------------------------------------

    //new date table

    VAR __rlvntDateTable =

    CALCULATETABLE(

    'Sheet1 (2)',

    ALL( 'Sheet1 (2)' ),

    'Sheet1 (2)'[Year and Qtr]=selQtr

    filter('Sheet1 (2),'[DATE]>O),filter('Sheet1 (2)','Sheet1 (2)'[DATE]<=m))

     

    ---------------

    VAR _altsalesamount =

    CALCULATE( SUM('Sheet1 (2)'[Sales], __rlvntDateTable )

    RETURN

    _altsalesamount

     below screen shot i have click multiple quarter , but it should work one click like 2019-4

     

    Please help me to get this done.
    Thanks in Advance.



    ------------------------------
    Mohamed Azarudeen
    BI Developer
    IV
    chennai
    9790777218
    ------------------------------

    Attachment(s)

    pbix
    sampleqtr.pbix   72K 1 version
    xlsx
    test.xlsx   39K 1 version
    Academy - Online Interactive Learning from Experts