Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Complex transaction calculation via User controlled date range

Hi All,   I have a problem that I'm unable to solve in PBI. We have used Sisense, where we write SQL queries and pass user input dynamically and use it in where filter clause and case statements. ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    You are welcome.  Go to the Query Editor and review the steps in the Applied steps pane.  Yes, the file size will increase but i do not know of a better way to solve this.

v-deddai1-msft's avatar
v-deddai1-msft
Community Support
5 years ago

Hi Anonymous ,

 

If you are trying to sum up values in fiscal year, you can create the following date table and create a one to many relationship to your transcation table:

 

Calendar = 
VAR WeekStartsOn = "Mon"
VAR FiscalStartMonth = 7 
RETURN
    ADDCOLUMNS (
        CALENDARAUTO ( FiscalStartMonth - 1 ),
        "FiscalMIndex", MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ),
        "Fiscal Week",
        VAR FiscalFirstDay =
            IF (
                MONTH ( [Date] ) < FiscalStartMonth,
                DATE ( YEAR ( [Date] ) - 1, FiscalStartMonth, 1 ),
                DATE ( YEAR ( [Date] ), FiscalStartMonth, 1 )
            )
        VAR FilteredTableCount =
            COUNTROWS (
                FILTER (
                    SELECTCOLUMNS ( GENERATESERIES ( FiscalFirstDay, [Date] ), "Dates", [Value] ),
                    FORMAT ( [Dates], "ddd" ) = WeekStartsOn
                )
            )
        VAR WeekNos =
            IF (
                FORMAT ( FiscalFirstDay, "ddd" ) <> WeekStartsOn,
                FilteredTableCount + 1,
                FilteredTableCount
            )
        RETURN
            "Week " & WeekNos,
        "Fiscal Qtr", "Q"
            & CEILING ( MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ), 3 ) / 3,
        "Fiscal Year",
        VAR CY =
            RIGHT ( YEAR ( [Date] ), 2 )
        VAR NY =
            RIGHT ( YEAR ( [Date] ) + 1, 2 )
        VAR PY =
            RIGHT ( YEAR ( [Date] ) - 1, 2 )
        VAR FinYear =
            IF ( MONTH ( [Date] ) > ( FiscalStartMonth - 1 ), CY & "-" & NY, PY & "-" & CY )
        RETURN
            FinYear,
        "CalWeekNo", WEEKNUM ( [Date], 2 ),
        "Weekend/Working", IF ( WEEKDAY ( [Date], 2 ) > 5, "Weekend", "Working" ))

 

 

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

 

Best Regards,

Dedmon Dai