passing date parameter in calculated measure like Tableau

[Hakuchha]

Hi,

I have a Tableau report which needs to be converted to PowebI. In Tableau, I have a date parameter and it is passed into two different date columns in the table. How do I replicate that in Power BI?

Here is an example 

name of parameter = dateparm

and here is 1st calculation

IFNULL( IF
([Policy Date]>= (datetrunc('month',[dateparm]) ) and
[Policy  Date]<=[dateparm])

[Revenue]
ELSE
0

End,0)

here is 2nd calculation in the same report

IFFNULL( IF
([Post Date]>= (datetrunc('month',[dateparm]) ) and
[Post Date]<=[dateparm])

)
Then
[TotalSales]
ELSE
0

End,0)

[v-yalanwu-msft]

Hi, @Hakuchha ;

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @Hakuchha ;

As far as I know it is possible. As follows:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Hakuchha]

somehow this is not working if I  do not display either pol date or postdate. is it possible to make it work without showing any dimensions? just those two calculated measures.

[Hakuchha]

instead of using a calculated parameter table, can I use a standalone date table from my DB for this? some how its not working.

[v-yalanwu-msft]

Hi, @Hakuchha ;

You could create a parameter table.

parameter = CALENDAR(DATE(2022,1,1),TODAY())

Then create measures.

revenuesum = IF(MAX('Table'[pol date])>=EOMONTH( MAX('parameter'[dateparm]) ,-1)+1&& MAX('Table'[pol date])<=MAX('parameter'[dateparm]),SUM('Table'[revenue]),0)

salessum = IF(MAX('Table'[pol date])>=EOMONTH( MAX('parameter'[dateparm]) ,-1)+1&& MAX('Table'[pol date])<=MAX('parameter'[dateparm]),SUM('Table'[sales]),0)

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Hakuchha]

thanks for your help. Can we display dateparm box as a calender instead of the list where users can pick any value easily.

[Hakuchha]

if you look at the above table, it has postdate and pol date which is a "policy date" and revenue and sales.

"'dateparm" is a user input parameter that will calculate two new fields for the report using the above calculation.

[Hakuchha]

postdate	pol date	revenue	sales
8/22/2022	8/16/2022	94513	68429.3
8/25/2022	8/28/2022	19772	144259.8
8/9/2022	8/9/2022	2255038	184271.8
8/4/2022	2/1/2022	712.97	4538.83
8/16/2022	7/31/2022	268.9	19288.68
8/17/2022	11/5/2021	97200.8	4944.97
8/11/2022	9/5/2021	2480.96	-706.1
8/3/2022	10/5/2021	-2961	-370.12
8/26/2022	3/17/2022	1268	17.68
8/10/2022	8/7/2022	450484	35184.51
8/9/2022	5/18/2022	-2718	-643.73
8/17/2022	11/8/2021	1316	1001.36
8/17/2022	4/1/2022	123641.6	4950.57
8/15/2022	8/19/2022	6498	6855.62

[parry2k]

@Hakuchha it will be easier if you paste a sample data with the expected output. 

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.